Scenario:
I have a table with 100M+ rows of data so I have created an 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 sent through the 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 to be executed while preventing from SQL injection and with minimal execution time.
I will be here if you need any further information.
Best Answer
@marcello has the syntax right, but another thing, to prevent SQL injection you can check the values of
@sort_col
and@sort_dir
(since I assume those are coming from the user). The direction is easy:Column names are a little more involved. You can just use constants to limit to the output columns from the query:
Or you can make sure the column at least exists in the table, which would allow you to change the columns in the query without having to change the
NOT IN
list, and also to allow sorting by a column not present in the output of the query (even if that might not always make sense):I wrote about this and other techniques in a two-part series: