1

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.

  • Msg 208, Level 16, State 1, Line 4 Invalid object name 'dbo.base_voter'. – Aayush Dahal Oct 10 '18 at 8:52
  • you CANNOT use variables in ORDER BY. Your code now looks like ORDER BY @sort_col @sort_dir but it's not supported syntax – sepupic Oct 10 '18 at 8:58
  • 2
    Check out Conditional Order By for some options – Scott Hodgin Oct 10 '18 at 9:09
1

@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:

IF LOWER(@sort_dir) NOT IN (N'asc', N'desc')
BEGIN
  RAISERROR(N'Invalid direction', 16, 1);
  RETURN;
END

Column names are a little more involved. You can just use constants to limit to the output columns from the query:

IF LOWER(@sort_col) NOT IN 
(
  N'id', 
  N'name_voter', 
  N'home_street_address_1', 
  N'home_address_city'
)
BEGIN
  RAISERROR(N'Invalid column', 16, 1);
  RETURN;
END

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):

IF NOT EXISTS
( 
  SELECT 1 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(N'dbo.base_voter')
      AND LOWER(name) = LOWER(@sort_col)
)
BEGIN
  RAISERROR('Invalid column', 16, 1);
  RETURN;
END

I wrote about this and other techniques in a two-part series:

2

from the comments of @sepupic and @Scott Hodgin, I put this together:

conditional order by

DECLARE @sql nvarchar(MAX);  
DECLARE @params nvarchar(100);  
declare @sort_col nvarchar(20) = 'name_voter'
declare @sort_dir nvarchar(20) = 'asc'

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

SET @params = N' @offset int, @limit int';

--print @sql

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.