How to pass parameters in dynamic sql [Resolved]

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

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                    

sravan

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