Use Variable with Top in Select Statement in SQL Server Without Making It Dynamic

Use variable with TOP in select statement in SQL Server without making it dynamic

Yes, in SQL Server 2005 it's possible to use a variable in the top clause.

select top (@top) * from tablename

Dynamic SELECT TOP @var In SQL Server

SELECT TOP (@count) * FROM SomeTable

This will only work with SQL 2005+

SQL: How do I use parameter for TOP like in SELECT TOP @amount?

Need parenthesis, and only for SQL Server 2005 and above

SELECT TOP (@param1) ...

How can I create a dynamic Select statement within a SQL Select statement?

Dynamic SQL is usually about

• Creating a variable that contains the exact SQL you want to run
• Then using EXEC (@SQLvariable) to run that code

For example (not for production yet!) I've added a new variable @CustomSQL

DECLARE @ValueList varchar(Max);
DECLARE @TSQL varchar(Max);
DECLARE @CustomSQL varchar(Max);

SET @TSQL = {stored proc to get base query}

SET @CustomSQL =
    'SELECT COALESCE(@ValueList + '','', '''') + CAST(Val AS varchar(max))
     FROM (
          ' + @TSQL + '
          ) As ValuesThisYear;'

PRINT @CustomSQL
EXEC (@CustomSQL)

Notice that adding text/strings (e.g., the @TSQL variable) have to be entered as exact strings rather than their variable names. Also note apostrophes - you need to use '' every time you wish to refer to a '.

I also removed the variable name from the SELECT @ValueList = ... because the dynamic SQL cannot actually reference the variables - it has its own scope (?cannot remember the correct word) and doesn't have access to the variables. Solutions to this include

• Using a temporary table e.g., #temp which can be referenced
• Using the OUTPUT clause

Personally, I would approach it a different way - use the T-Sql provided to put data into a temporary table. Then use the temporary table in the other statement e.g.,

DECLARE @ValueList varchar(Max);
DECLARE @TSQL varchar(Max);
SET @TSQL = {stored proc to get base query}

DECLARE @CustomSQL varchar(Max)
CREATE TABLE #temp (Val varchar(1000))
SET @CustomSQL = 'INSERT INTO #temp (Val) ' + @TSQL
EXEC (@CustomSQL)

SELECT @ValueList = COALESCE(@ValueList + ',', '') + CAST(Val AS varchar(max))
FROM #temp As ValuesThisYear;
PRINT @ValList

I almost never get my dynamic SQL correct first try. Suggestions

• Keep it as simple as possible
• Before having a version that runs (e.g., EXEC (@CustomSQL)), comment the EXEC out and PRINT it instead.

Here are some examples from previous posts I've done recently

• Query for R Machine Learning Services - Filtering Categories in Where Clause
• Bottom of Dynamic columns depend on previous dynamic columns - TSQL

Dynamic TOP N / TOP 100 PERCENT in a single query based on condition

A better solution would be to not use TOP at all - but ROWCOUNT instead:

SET ROWCOUNT stops processing after the specified number of rows.

...

To return all rows, set ROWCOUNT to 0.

Please note that ROWCOUNT is recommended to use only with select statements -

Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.

DECLARE @V_COUNT INT = 0

SET ROWCOUNT @V_COUNT -- 0 means return all rows...

SELECT   *
FROM    MY_TABLE
ORDER BY COL1

SET ROWCOUNT 0 -- Avoid side effects...

This will eliminate the need to know how many rows there are in the table

Be sure to re-set the ROWCOUNT back to 0 after the query, to avoid side effects (Good point by Shnugo in the comments).

SQL Server - use a parameter to select the top X of the result set

In SqlServer 2005 and up, do this:

CREATE PROCEDURE GetResults    (
    @ResultCount   int
)
AS

SELECT top(@ResultCount) FROM table where x = y

For earlier versions, use:

CREATE PROCEDURE GetResults    (
    @ResultCount   int
)
AS

SET ROWCOUNT @ResultCount

SELECT * FROM table where x = y

https://web.archive.org/web/20210417081325/http://www.4guysfromrolla.com/webtech/070605-1.shtml for more information.

Why do I need to declare variables twice: within and without the dynamic query?

For the same reason that:

• A variable is declared in a stored procedure
• That procedure calls a second procedure
• The variable declared in the first procedure is not accessible in the second procedure.

In other words: scope. The dynamic code is executed as it's own batch, process, session... thing, I'm sorry but I don't know the exact technical term as used by SQL.

Check out sp_ExecuteSQL's support for parameters. It's fussy and complex but will allow you to pass parameter values into (and out of!) your dynamic SQL.

Can I use a variable instead of the integer in OVER clause

I was hoping that (Select @MA) would work, but alas no luck

Perhaps you can go dynamic

Declare @MA int = 50

Declare @SQL varchar(max) ='Select Date, price, avg(price) over( order by Date, Date rows between '+cast(@MA as varchar(25))+' preceding and current row) as moving_avg from t1 '
Exec(@SQL)

Using variables within a dynamic sql script

You can pass through variables to dynamic SQL via sp_executesql

• Note that you should always use QUOTENAME to escape object names
• Also, dynamic SQL variables should always be nvarchar
• You also should not use variable coalescing to aggregate, instead use STRING_AGG or FOR XML

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))

            
SET @sAlterTableDynamicSQL =
'
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N'','')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N''tempdb..' + QUOTENAME(@sNomTableTemporaire, '''') + ''');
';

PRINT (@sAlterTableDynamicSQL);

EXEC sp_executesql
  @sAlterTableDynamicSQL,
  N'@sColumns nvarchar(max) OUTPUT'
  @sColumns = @sColumns OUTPUT;

But you don't actually need dynamic SQL here at all. You can pass the table name straight to object_id()

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))
            
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N',')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N'tempdb..' + QUOTENAME(@sNomTableTemporaire));

For SQL Server 2016 and earlier, you can use the FOR XML PATH('') method

---

Related Topics