Dynamic Select Top @Var in SQL Server

Dynamic SELECT TOP @var In SQL Server

SELECT TOP (@count) * FROM SomeTable

This will only work with SQL 2005+

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

Create Dynamic Query in Sybase SELECT TOP X

The answer is:

DECLARE @DEPTH INT
SET @DEPTH = 8    
SET ROWCOUNT @DEPTH
SELECT * FROM Table
SET ROWCOUNT 0

Variable as top value

SELECT TOP (@num) a FROM table

SQL server 2005 onwards, this is supported to parameterize TOP.

Set multiple variables using one SQL query that returns one row using SELECT TOP

You can use TOP (n) with SELECT statement :

SELECT TOP (1) @var1 = col1, @var2 = col2, ..., @varn = coln 
FROM table 
WHERE col = @col 
ORDER BY id DESC;

Sql server use variable in selection top row

You have to enclose the variable in parenthesis to make it work.

declare @i int ;
set @i = 10 ;
select top (@i) * from tableNam

If you take the cursor to the error line you will find the reason as to why you need the parenthesis like this:

So the value should be a integer. Also if you see the TOP keyword from MSDN then it says to use the parenthesis. The syntax said by MSDN is:

[ 
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

XQuery [value()] issue for Dynamic SQL variable in Select Stateemt

I think

SELECT
  C.value('@name','NVARCHAR(MAX)') AS [Page Name],
  C.query('{node()}') AS [Page_XML], 
  ROW_NUMBER() OVER (ORDER BY t.c)
FROM @opxml.nodes('/level1/level2/template/page') AS T(C)

might suffice to produce the wanted result without the need for a WHILE loop.

I think the original problem with the long path inside of value() (e.g. C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")]','NVARCHAR(MAX)')) is due to the static type checking of XQuery in SQL server and to avoid it you basically need to add another predicate that ensures the type checker knows a single value is returned e.g. C.value('(/level1/level2/template/page/@name)[sql:variable("@Counter")][1]','NVARCHAR(MAX)')

For me, the code

DECLARE @opxml AS XML

SET @opxml = N'
  
    
  
'

SELECT
  C.value('@name','NVARCHAR(MAX)') AS [Page Name],
  C.query('{node()}') AS [Page_XML], 
  ROW_NUMBER() OVER (ORDER BY t.c)
FROM @opxml.nodes('/level1/level2/template/page') AS T(C)

produces the table

Page Name   Page_XML    (Kein Spaltenname)
Test Page Name  

              Text
            
 1
Properties  
Created by Joe Bloggs
Date published 30/05/2017
 2

so at least the Page_Name seems to be easily populated by using e.g. C.value('@name','NVARCHAR(MAX)') AS [Page Name]

---

Related Topics