how to convert this value into a stored procedure variable? 1

[childrenfirst]

Hi,

I have a query and it works, but I am trying to pass a variable to the query and failed at this point.

Original query (and worked!):

SELECT Top 5 counter
FROM Employee
ORDER BY NewID()

Try To (and did not work...):

DECLARE @selectnumber AS int

SELECT Top @selectnumber counter
FROM Employee
ORDER BY NewID()


Any advice will be greatly appreciated!!

 

[TomKane]

You would have to do it as Dynamic SQL such as:

DECLARE @sql AS varchar
DECLARE @selectnumber AS int

SET @sql = 'SELECT Top ' + CAST(@selectnumber AS varchar) + ' counter FROM Employee ORDER BY NewID()'

EXEC (@sql)

 

[TysonLPrice]

DECLARE @selectnumber AS int
set @selectnumber = 5
set rowcount @selectnumber
SELECT counter
FROM Employee
ORDER BY NewID()
set rowcount 0

 

[SQLDenis]

use set rowcount

DECLARE @selectnumber AS int
set @selectnumber = 5

set rowcount @selectnumber

SELECT  counter
FROM Employee
ORDER BY NewID()

set rowcount 0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

You don't need dynamic SQL to do this.

DECLARE @selectnumber AS int
Set @SelectNumber = [!]8[/!] [green] -- As An Example[/green]

[!]Set RowCount @selectnumber[/!]

SELECT counter
FROM Employee
ORDER BY NewID()

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

TysonLPrice ,
wow it almost looks as if i copied the exact same query lol

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

I'm not feeling well, so I'm going back to bed.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[childrenfirst]

Hi All,

Thank you so much for brainstorming for me. In the stored procedure I am working on, a dynamic SQL will work better Thank you all! Happy holiday

 

[TomKane]

Hi,

Glad I could help. Thanks for the star!

Regards,
Tom