Both are the only ways to do that in SQL Server (the second was the only way with SQL 6.5) but when using SET ROWCOUNT @var be sure to do a SET ROWCOUNT 0 to turn it off. Even though BOL says all SET operations are cancelled after the proc is finished, don't take a chance.
David L. Penton, Microsoft MVP JCPenney Technical Specialist / Lead "Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach" [EMAIL PROTECTED] Do you have the VBScript Docs or SQL BOL installed? If not, why not? VBScript Docs: http://www.davidpenton.com/vbscript SQL BOL: http://www.davidpenton.com/sqlbol -----Original Message----- From: Andrew Zetterman [mailto:[EMAIL PROTECTED]] How about this? CREATE PROC sp_MyProc @AMaxRows int AS BEGIN SET ROWCOUNT @AMaxRows select * from cust END -----Original Message----- From: Scott [mailto:[EMAIL PROTECTED]] You could pass your MaxRows variable into a proc and build your SQL string. CREATE PROC sp_MyProc @AMaxRows int AS BEGIN set nocount on declare @sql varchar(255) set @sql = 'select top ' + cast(@AMaxRows) + ' * from cust' exec (@sql) END This is the only way I know of and have used. Tore? David? hope that helps anyway ----- Original Message ----- From: "Tyler Brown" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Wednesday, August 21, 2002 12:56 AM Subject: dynamic "top *" sql statement? : is it possible to use a variable in a "top #" select statement within a : stored procedure? : : When I try declaring and using a value I get an ado error: "incorrect syntax : near [variable name]" : : How does one get around this without dynamically creating the sql string? --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
