CODEWITHSUNDEEP
sqlsql-server
Pablo
Pablo

Reputation: 5087

Retrieving value from EXEC(@sql)

The title of the question might not be the best but I couldnt find another , if a mod finds a more accurate description of the problem, it's welcome to edit it.

I need to build a dymanic sql query which has variable parameter in the top clause like

SELECT top (value) field, field2 FROM table

Now the former query it's actually a subquery from this query

SELECT @first = min(field2) FROM (SELECT top (value) field, field2 FROM table)

which means I need exec(@sql) to build the query because sql top doesnt take dynamic values, but exec(@sql) doesnt allow me to retrieve a parameter as far as I know. I've read in similar case to use sp_executesql to retrieve a value, but I cant build the query with the dynamic value next to the top clause, I believe . What can I do?

Upvotes: 1

Views: 213

Answers (2)

SQLMenace
SQLMenace

Reputation: 135111

if you are on sql server 2005 or above you can use a variable to do a dynamic top, if you are still on 2000, you can use SET ROWCOUNT with a variable

2005 and up syntax

DECLARE @RowCount int -- =25 -- sql server 2008 allows to assign when declaring
SELECT @RowCount = 25 

SELECT TOP (@RowCount) * FROM table1

2000 syntax

DECLARE @RowCount int 
SELECT @RowCount = 25 

SET ROWCOUNT @RowCount
SELECT  * FROM table1

SET ROWCOUNT 0  -- make sure to set back to 0 otherwise 
                -- queries below will return 25 rows

Upvotes: 2

David Brabant
David Brabant

Reputation: 43539

declare @top int = 10

select top (@top) field, field2 from table

Upvotes: 2

Related Questions