Local variables in store procedure (or SQL script)

[yablonka]

Hi all,

 

I'm writing a very simple SQL script that performs a simple "select" query. The thing is that I want to use a "top" selection (select top 20 * from MyTable), however I want to use a parameter instead of a fixed number (select top X * from MyTable)

I tried this:

 

declare @MyTop as int

set @MyTop=20

select top @MyTop * from MyTable

 

Well...it doesn't work.

I'm getting a "Incorrect syntax near '@MyTop'." message.

Any idea how I should do this?

 

Thanks.

P.s. - I'm working with MSSQL.

[PlausiblyDamp]

the top clause doesn't support variables - then again it isn't ANSI standard anyway ;(

 

you will need to set the rowcount variable to limit the maximum number of rows...

 

declare @rows int
set @rows = 10

SET ROWCOUNT @rows

select * from customers

[Robby]

Actualy it can be done....

 

You can even soft-code the table or column names if needed.

declare @MyTop as int
set @MyTop=20
declare @Sql varchar(1000)
DECLARE @Crlf	NCHAR(2)
SET @Crlf = CHAR(13) + CHAR(10)

set @Sql = 	'BEGIN  TRANSACTION' + @Crlf +
	'select top ' + @MyTop +  ' * from MyTable' + @Crlf +
	'COMMIT TRANSACTION' + @Crlf 	

Begin transaction
exec (@Sql)
commit transaction

[yablonka]

Thank you...

Both replys can benefit me....