Parameterise values for OFFSET and FETCH NEXT

Question

Is there any way to do it ?

I'd like to be able to run it as dynamic sql via EXEC sp_executesql

At the moment - it's just a string concatenation

like so:

SET @sql += '
ORDER BY em.[Date] ' + IIF (@sortOrder = 'asc', 'ASC', 'DESC') + '
OFFSET ' + CONVERT (VARCHAR, @pageNum*@pageSize) + ' ROWS
FETCH NEXT ' + CONVERT (VARCHAR, @pageSize) + ' ROWS ONLY'

Ideally, I'd like it to be:

SET @sql += '
ORDER BY em.[Date] ' + IIF (@sortOrder = 'asc', 'ASC', 'DESC') + '
OFFSET @offset ROWS
FETCH NEXT @rows ROWS ONLY'

Answer 1

You can execute a dynamic TSQL command containing variables by passing their values when calling sp_executesql.

This should work:

DECLARE @sql nvarchar(max)  
DECLARE @parameters nvarchar(max) 
DECLARE @tmp_offset int

--create dynamic sql command with variables
SET @sql += '
ORDER BY em.[Date] ' + IIF (@sortOrder = 'asc', 'ASC', 'DESC') + '
OFFSET @offset ROWS
FETCH NEXT @rows ROWS ONLY'

--calculate offset
SET @tmp_offset =  @pageNum * @pageSize

--define the parameters that will be used inside dynamic SQL
SET @parameters = N'@offset int, @rows int'   

--execute dynamic sql passing variables' values
EXECUTE sp_executesql @sql, @parameters, @offset = @tmp_offset, @rows = @pageSize