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.