Using A Variable In A Stored Procedure To Select The TOP n Rows

Question

question

David 2 1 asked May 27, '10

Using A Variable In A Stored Procedure To Select The TOP n Rows

Can anyone explaing why the below is erroring?

CREATE PROCEDURE split @tabname VARCHAR(20), @num CHAR(2)                    
AS                    
SET NOCOUNT ON                    
                    
DECLARE @split VARCHAR(8000)                    
SELECT @split = '                    
                    
DECLARE @numrows VARCHAR(10)                     
SELECT @numrows = COUNT(*)/' + @num + ' FROM ' + @tabname + '                    
                    
INSERT INTO ' + @tabname + '_temp                    
SELECT TOP @numrows * FROM ' + @tabname + '                     
WHERE client NOT IN (SELECT client FROM ' + @tabname + '_temp)                    
ORDER BY NEWID() '                    
EXEC (@split)

Thanks in advance.

edit: It generates this error:

Server: Msg 170, Level 15, State 1, Line 5                    
Line 5: Incorrect syntax near '@numrows'.

t-sql sql-server-2000

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz ♦ commented · May 27, 2010 at 09:55 AM

Can you print out the SQL that it generates and post it here?

1 ·

Answer 1 · 2010-05-27T11:11:14Z

Squirrel 1 answered May 27, '10

you can also use SET ROWCOUNT to achieve that.

try.

declare @sql nvarchar(4000),            
        @numrows int                    
            
select @sql = N'            
select @numrows = count(*) / @num            
from   ' + @tabname            
            
exec sp_executesql @sql, N'@num int, @numrows int OUTPUT', @num, @numrows OUTPUT            
            
select @sql = N'            
INSERT INTO ' + @tabname + '_temp            
SELECT * FROM ' + @tabname + '             
WHERE client NOT IN (SELECT client FROM ' + @tabname + '_temp)            
ORDER BY NEWID() '            
            
set rowcount @numrows            
exec (@sql)            
set rowcount 0

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-05-27T10:21:36Z

Squirrel 1 answered May 27, '10

SQL 2000 does not support variable in TOP

you will need 2 dynamic sql statement. One to get the @numrows and another is the actual query that you need.

for the 1st dynamic sql query, you can use sp_executesql which allows you to pass in and out a variable.

As for the 2nd query should change to

SELECT TOP ' + @numrows + ' * FROM ' + @tabname + '

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question