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

LVL 4
karakavAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
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
0
HoggZillaCommented:
When I run it as you have it, this is my result. Notice, the date needs quotes, RownN needs to be defined by alias:
SELECT * FROM (      SELECT CustomerID, ContractName, RequiredDate,          ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowN FROM [dbo].[OrdersByContact]( 'CONTACT_BOB',12-25-2005)) T1      WHERE T1.RowN BETWEEN 17 AND 23 ORDER BY OrderDate DESC
I could fix for you, but you already know how to do that - I can tell from what you already have here. What the script does below is allow you to see the query based on what you might pass the procedure.

DECLARE @ContactName AS VARCHAR(20);
DECLARE @OrdDate AS VARCHAR(10);
DECLARE @StartIndex AS VARCHAR(20);
DECLARE @EndIndex AS VARCHAR(20);
DECLARE @Udf AS VARCHAR(MAX);
DECLARE @Columns AS VARCHAR(MAX);
DECLARE @OrderColumn AS VARCHAR(MAX);
DECLARE @OrderDirection AS VARCHAR(10);
DECLARE @QUERY AS NVARCHAR(MAX);
 
SET @ContactName = 'CONTACT_BOB';
SET @OrdDate = '12-25-2005';
SET @StartIndex = '17';
SET @EndIndex = '23';
SET @Udf = '[dbo].[OrdersByContact]';
SET @Columns = 'CustomerID, ContractName, RequiredDate';
SET @OrderColumn = 'OrderDate';
SET @OrderDirection = 'DESC';
 
  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;
 
SELECT @QUERY

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karakavAuthor Commented:
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.
0
HoggZillaCommented:
Yes, change it to VARCHAR(12)
SET @OrdDate = '12-25-2005'
SET @OrdDate = '''' + @OrdDate + ''''
SELECT @OrdDate
'12-25-2005'
0
karakavAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.