CODEWITHSUNDEEP
sqlsql-server
Eliad Ayehu
Eliad Ayehu

Reputation: 125

sql query not working if come as a string

I'm trying to exec sql SP, and add a "select" to the query. it is working fine, but if I add the "between" I get an error.

this is the code:

DECLARE @query as nvarchar(200);
    SET @query = N'select *
                    from (    
                    select *,totalRecords = COUNT(*) OVER(),ROW_NUMBER() OVER ( ORDER BY date desc) AS RowNum
                        from  #data
                    ) as RowConstrainedResult
                    where RowNum  between 1 and 20
                    ORDER BY RowNum';

    EXEC sp_executesql @query

and this is the error:

 Incorrect syntax near '1'.

Upvotes: 1

Views: 133

Answers (4)

hazem atia
hazem atia

Reputation: 1

you have declared @query as nvarchar(200) and the query length is more than 200 so just change @query as nvarchar(200) to @query as nvarchar(2000) and it will work fine with you .

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Problem with the variable declaration

DECLARE @query as nvarchar(200);
                           ^^^

Since your variable contains multi line sql statement that is not enought for this variable, try to use MAX size variable for this multi line sql statement 

DECLARE @query as nvarchar(MAX);

Upvotes: 1

TriV
TriV

Reputation: 5148

I think problem is in DECLARE @query as nvarchar(200);

Change it to DECLARE @query as nvarchar(MAX);

You should PRINT @query to check your query before executing

Upvotes: 0

Whitewolf
Whitewolf

Reputation: 186

Try this:

DECLARE @query as nvarchar(200);
SET @query = 'select * from (select *,totalRecords = COUNT(*) OVER(),ROW_NUMBER() OVER ( ORDER BY date desc) AS RowNum from  #data) as RowConstrainedResult where RowNum between 1 and 20 ORDER BY RowNum';

  select @query              

EXEC sp_executesql @query

Upvotes: 0

Related Questions