Dynamic SQL query Issue - SQL Server

Question

I'm writing a dynamic query for paging purpose. below is the last part of the executable query and when i print it and run in different window it works fine. but when i use exec it shows red squiggle for casting parts (CAST(@pint_StartRowIndex AS VARCHAR)) as showing in attached image.

could anyone tell me why I'm getting this error? If i use Print instead of Exec it gives no errors and printed query execute with no errors.

Build your command first and then pass it to exec, i.e. declare @sqlcommand nvarchar(max);set @sqlcommand = 'this ' + that; exec(@sqlcommand) — artm
– artm, Commented Oct 24, 2016 at 6:01
@artm i did it at first place. But there will be additional where conditions to the query. in this case i used minimum conditions. as i know varchar(max) can hold only 8000 characters. i have doubt that with additional conditions this limit might exceed. That's why i tried to execute as above — user2837480
– user2837480, Commented Oct 24, 2016 at 6:07

James · Accepted Answer · 2016-10-24 06:14:00Z

1

try like this :

exec ('SELECT * from table where Rank BETWEEN CAST ('+ @start + 'as nvarchar)' + ' AND cast (' +  @end + 'as nvarchar)')

quote everything as string except @start and @end

answered Oct 24, 2016 at 6:14

James

7276 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic SQL query Issue - SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related