Syntax error: Dynamic SQL in sql server

Transact-SQL
1

Scenario:

I have a table with 100M+ rows of data so I have created index with something as:

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt  
    ON dbo.base_voter (name_voter,home_street_address_1, home_address_city)  
    WHERE deleted_at IS NULL ;

Now when I execute the following code, it executed in less time:

SELECT id,
name_voter,
home_street_address_1,
home_address_city
FROM dbo.base_voter
WITH(NOLOCK)
WHERE deleted_at IS NULL
order by name_voter asc
OFFSET 0 ROWS 
FETCH NEXT 50 ROWS ONLY

Now, I want to create this query as dynamic sql as these parts name_voter asc, 0 , 50 need to be dynamic and will be send through backend.

But, when I try to execute

DECLARE @sql nvarchar(MAX);  
DECLARE @params nvarchar(1000);  
 
SET @sql =  
	 N'SELECT id, name_voter, home_street_address_1,home_address_city  
	   FROM dbo.base_voter 
	   WITH(NOLOCK)
	   ORDER BY @sort_col @sort_dir
	   OFFSET @offset ROWS
	   FETCH NEXT @limit ROWS ONLY';  
SET @params = N'@sort_col nvarchar(100),@sort_dir nvarchar(10), @offset int, @limit int';
 
 
EXECUTE sp_executesql @sql, @params,  
					  'name_voter','asc', 0, 50;

I receive error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@sort_dir'.
Msg 153, Level 15, State 2, Line 9
Invalid usage of the option NEXT in the FETCH statement.

I am not sure but is it because I am passing column name as a parameter? Or is there some other thing. I just want that query be executed while preventing from sql injection and with minimal execution time.

I will be here, if you need any further information.

3

Hi

One thing you can do
Is

Add
Print @sql

At the end

See the SQL statement

Try to run it .. error comes
Fix the error

What you fixed
Go back to dynamic SQL
Part and fix

4

image
image.png2277×1369 275 KB

5

One cannot parametrise the ORDER BY with

ORDER BY @sort_col @sort_dir

When I tried this:

ORDER BY @sort_col ASC

this error message showed : The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

When I tried this

ORDER BY name_voter @sort_dir

I got your error message Invalid usage of the option NEXT in the FETCH statement.

You have to revert to (real) dynamic SQL

ORDER BY ' + @sort_col + ' ' + @sort_dir +

This works:

DECLARE @sql nvarchar(MAX);  
DECLARE @params nvarchar(1000);  

DECLARE @sort_col nvarchar(100) = 'name_voter'
DECLARE @sort_dir nvarchar(10) = 'asc'

SET @sql =  
	 N'SELECT id, name_voter, home_street_address_1,home_address_city 
	   FROM dbo.base_voter 
	   WITH(NOLOCK)
	   ORDER BY ' + @sort_col + ' ' + @sort_dir + 
'	   OFFSET @offset ROWS
	   FETCH NEXT @limit ROWS ONLY';  
SET @params = N'@offset int, @limit int';

EXECUTE sp_executesql @sql, @params, 0, 50;

Watch out for code injection.