asked on

T-SQL,SQL SERVER: Dynamically passing parameters to UDF inside a stored procedure

Hi, I have the following UDF and SP as in the snippet. I would like to pass in parameters dynamically but when I fail when the parameter passed in is a varchar. How can I correct that? I know the problem is the beginning and ending quote but I don't how to include that.

--UDF
 
USE [Northwind]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[OrdersByContact]
(	
	@CusId VARCHAR(20),
	@OrdDate DATETIME
 
)
RETURNS TABLE 
AS
RETURN 
(
	WITH Ord AS
	(
		SELECT	C.CustomerId,
				C.ContactName,			
				O.OrderDate,
				O.RequiredDate
				
		FROM Customers C INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId
	)
 
	SELECT * FROM Ord 
	WHERE CustomerId =  @CusId AND OrderDate = 
	CASE 
		WHEN @OrdDate IS NOT NULL THEN @OrdDate
		ELSE OrderDate
	END
)
 
--SP
 
USE [Northwind]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SortUdf] 
	@ContactName VARCHAR(20),
	@OrdDate VARCHAR(10),
	@StartIndex VARCHAR(20),
	@EndIndex VARCHAR(20),
	@Udf VARCHAR(MAX),
	@Columns VARCHAR(MAX),
	@OrderColumn VARCHAR(MAX),
	@OrderDirection VARCHAR(10)
AS
BEGIN
	DECLARE @QUERY NVARCHAR(MAX)
 
	IF (@OrdDate IS NULL) SET @OrdDate = 'NULL'
 
  SET @QUERY =
  'SELECT * FROM (
    SELECT ' + @Columns + ',
        ROW_NUMBER() OVER (ORDER BY '+ @OrderColumn + ') AS RowN FROM ' + @Udf + '( '''
    + @ContactName + ''','
    + @OrdDate  + ')) T1
    WHERE T1.RowN BETWEEN ' + @StartIndex + ' AND ' + @EndIndex
    + ' ORDER BY '+ @OrderColumn + ' ' + @OrderDirection;
 
	
 
PRINT(@QUERY)
EXEC sp_executesql @QUERY
 
END

Open in new window

tigin44

try this procedure

CREATE PROCEDURE [dbo].[SortUdf]
@ContactName VARCHAR(20),
@OrdDate VARCHAR(10),
@StartIndex VARCHAR(20),
@EndIndex VARCHAR(20),
@Udf VARCHAR(MAX),
@Columns VARCHAR(MAX),
@OrderColumn VARCHAR(MAX),
@OrderDirection VARCHAR(10)
AS
BEGIN
DECLARE @QUERY NVARCHAR(MAX)

SET @QUERY =
      N'SELECT * FROM (
SELECT @Columns, ROW_NUMBER() OVER (ORDER BY @OrderColumn AS RowN FROM @Udf ( @ContactName, @OrdDate)) T1
WHERE T1.RowN BETWEEN @StartIndex AND @EndIndex
ORDER BY @OrderColumn @OrderDirection';

PRINT(@QUERY)
EXEC sp_executesql @QUERY,
      N'@Columns VARCHAR(MAX), @OrderColumn VARCHAR(MAX), @OrderDirection VARCHAR(10),@ContactName VARCHAR(20),@OrdDate VARCHAR(10),@StartIndex VARCHAR(20),@EndIndex VARCHAR(20),@Udf VARCHAR(MAX))',
      @Columns, @OrderColumn, @OrderDirection, @ContactName, @OrdDate, @StartIndex, @EndIndex, @Udf
END

ASKER CERTIFIED SOLUTION

Steve Hogg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

karakav

ASKER

Thanks HoggZilla. I there a way of storing the quotes together with the value of the variable?
IF (@OrdDate IS NOT NULL)
SET @OrdDate = ''' + @OrdDate + '''

I tried that but without any success. What I wanted was making my query shorter.

SOLUTION