How to pass parameters in dynamic sql [Resolved]

Posted by Sharpcnet under Sql Server on 2/6/2014 | Points: 10 | Views : 1154 | Status : [Member] | Replies : 3
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





Responses

Posted by: Bandi on: 2/6/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
This is the working query... Go ahead...


DECLARE @SortOrder nchar(10),

@Query nvarchar(MAX),

@ColName nvarchar(20),

@Params nvarchar(MAX),

@StartIndex varchar(5),

@EndIndex varchar(5)



SET @SortOrder = 'desc'

SET @ColName = 'Quantity'



SET @StartIndex = 1

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 +';'

--PRINT @query
execute sp_Executesql @Query,@Params,@ColName,@StartIndex,@EndIndex,@SortOrder


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/6/2014 [Member] Bronze | Points: 25

Up
0
Down
Hi,
Use this code it works


DECLARE @SortOrder nchar(10),
@Query varchar(MAX),
@ColName varchar(20),
@Params varchar(MAX),
@StartIndex int,
@EndIndex int
SET @SortOrder = 'desc'
SET @ColName = 'Quantity'
SET @StartIndex = 10
SET @EndIndex = 20

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 ' + cast(@StartIndex as varchar(10))+ ' AND '+ cast(@EndIndex as varchar(10)) + ' ORDER BY Row '+ @SortOrder

exec @Query


Hope it helps you


Mark as Answer if you are satisfied

sravan

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/6/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Try this. I haven't tested the below. if any syntax error try to correct and execute; If not working post me back
CREATE PROCEDURE [dbo].[USP_Select_OrderDetails](
@P_PageIndex INT, -- Page Number
@P_PageSize INT, -- Number of rows per page
@P_SortColumn VARCHAR(60), -- Column Name for Sorting
@P_SortDirection VARCHAR(5) -- Either DESC = descending or ASC = ascending
)
AS
BEGIN
DECLARE @V_PageIndex INT;
SET @V_PageIndex = (@P_PageIndex-1)*@P_PageSize; -- Lower limit for row number

SELECT OrderID,ProductID,ProductName,UnitPrice,Quantity,Discount
FROM(
SELECT OD.OrderID,OD.ProductID,P.ProductName,OD.UnitPrice,OD.Quantity,OD.Discount
,ROW_NUMBER() OVER(ORDER BY CASE @P_SortDirection WHEN 'desc' THEN
CASE @P_SortColumn
WHEN 'Quantity' THEN Quantity
WHEN 'Discount' THEN Discount
when 'UnitPrice' then UnitPrice
ELSE OrderID END
END DESC,
CASE @P_SortDirection WHEN 'asc' THEN
CASE @P_SortColumn
WHEN 'Quantity' THEN Quantity
WHEN 'Discount' THEN Discount
when 'UnitPrice' then UnitPrice
ELSE OrderID END
END ASC) RowId -- generating row number based on SortColumn & Direction
FROM [Order Details] OD
LEFT JOIN Products P ON OD.ProductID = P.ProductID
) t
WHERE RowId BETWEEN @V_PageIndex+1 AND @V_PageIndex+@P_PageSize -- Filter Inbox Items by passing LowerLimit and UpperLimit of rows
ORDER BY
CASE @P_SortDirection WHEN 'desc' THEN
CASE @P_SortColumn
WHEN 'Quantity' THEN Quantity
WHEN 'Discount' THEN Discount
when 'UnitPrice' then UnitPrice
ELSE OrderID END
END DESC,
CASE @P_SortDirection WHEN 'asc' THEN
CASE @P_SortColumn
WHEN 'Quantity' THEN Quantity
WHEN 'Discount' THEN Discount
when 'UnitPrice' then UnitPrice
ELSE OrderID END
END ASC; -- Sorting Sent Items based on sort column and direction per page
END;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response