In this case, you would of course need to convert or cast the integer to a varchar or char.
Also, a bit of a nitpic: When creating all database objects that *can* be owner qualified, do it. Even for DBO: CREATE PROCEDURE "dbo"."myProcName" AS... CREATE TABLE "stan"."myTable" and call these object owner qualified. This is so the correct owner will be marked with the object, and the correct object will always be called. 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: Larry Hough [mailto:[EMAIL PROTECTED]] Yes it is possible to use a variable in a "top #" select statement within a stored procedure? Create Proc TopNumber @Number Int As Exec ('Select Top ' + @Number + ' Lastname from Employees Order by Lastname') I hope this is what you are looking for Larry Hough -----Original Message----- From: Andrew Zetterman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 12:11 PM To: ActiveServerPages Subject: RE: dynamic "top *" sql statement? 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]] Sent: Tuesday, August 20, 2002 11:07 AM To: ActiveServerPages Subject: Re: dynamic "top *" sql statement? 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]
