This query is to get data for a gridview, by page index. The column name, sorting order and the page index will be parameters that will be passed on.
Error: Incorrect syntax near @SortOrder
I am able to acheive the desired result if I break the @query by inserting '+' symbols before the variables
eg: '....order by row '+@SortOrder.
But would like to know, is there a way to get the result keeping the @query as it is now. I mean all the query in between one quote.
DECLARE @SortOrder nchar(10),
@Query nvarchar(MAX),
@ColName nvarchar(20),
@Params nvarchar(MAX),
@StartIndex int,
@EndIndex int
SET @SortOrder = 'desc'
SET @ColName = 'Quantity'
SET @StartIndex = 10
SET @EndIndex = 20
SET @Params = '@ColName nvarchar(20),
@StartIndex int,
@EndIndex int,
@SortOrder nchar(10)'
SET @Query = 'WITH TBL AS
(
SELECT OD.OrderID,OD.ProductID,P.ProductName,OD.UnitPrice,
OD.Quantity,OD.Discount
FROM [Order Details] OD
LEFT JOIN Products P ON OD.ProductID = P.ProductID
)
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY @ColName)Row, *
FROM TBL
)TEMP
WHERE Row BETWEEN @StartIndex AND @EndIndex
ORDER BY Row @SortOrder'
execute sp_Executesql @Query,@Params,@ColName,@StartIndex,@EndIndex,@SortOrder