• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

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

0
karakav
Asked:
karakav
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now