SQL Function with multiple parameters

Question

CREATE TYPE IdRange AS TABLE 
(
    IDValue NVARCHAR(MAX)
);

CREATE FUNCTION GET_CUSTOMER_WITH_ID
    (@myIDRange IdRange)
RETURNS @ReturnTable TABLE  
AS 
BEGIN
    SELECT  
        MY_CC.CustomerID AS [Customer ID], 
        CONCAT(MY_CC.FirstName, ' ', MY_CC.LastName) As Name, 
        MY_CC.City, MY_CC.State, MY_CC.ZipCode, 
        MY_CC.DateOfBirth, 
        MY_AS.EventType AS [Application Status], 
        MY_AS.EventDateTime AS [Timestamp], MY_AS.ExpirationDate
    FROM 
        [database].[dbo].[MY_CustomerCard] AS MY_CC
    INNER JOIN 
        [database].[dbo].[MY_ApplicationStatus] AS MY_AS ON MY_CC.CustomerID = MY_AS.CustomerID 
    WHERE 
        MY_CC.CustomerID IN (SELECT IDValue FROM @myIdRange)

    RETURN;

I want to create a function that does something like

SELECT 
    MY_CC.CustomerID AS [Customer ID], 
    CONCAT(MY_CC.FirstName, ' ', MY_CC.LastName) AS Name, 
    MY_CC.City, MY_CC.State, MY_CC.ZipCode, 
    MY_CC.DateOfBirth,
    MY_AS.EventType AS [Application Status], 
    MY_AS.EventDateTime AS [Timestamp], MY_AS.ExpirationDate
FROM 
    [database].[dbo].[MY_CustomerCard] AS MY_CC
INNER JOIN 
    [database].[dbo].[MY_ApplicationStatus] AS MY_AS ON MY_CC.CustomerID = MY_AS.CustomerID 
WHERE 
    CUSTOMERID >= @firstParameter AND CUSTOMERID <= @secondParameter

I am having trouble with getting the parameter value or knowing how to parse the parameters correctly.

Please help.

CREATE FUNCTION dbo.GET_CUSTOMER_WITH_ID
    (@startParam int, @endParam int)
RETURNS TABLE
WITH SCHEMABINDING -- better for performance, but blocks changes to underlying objects, forces two-part names
AS
    SELECT 
        MY_CC.CustomerID, 
        CONCAT(MY_CC.FirstName, ' ', MY_CC.LastName) AS Name, 
        MY_CC.City, MY_CC.State, MY_CC.ZipCode, 
        MY_CC.DateOfBirth, 
        MY_AS.EventType, MY_AS.EventDateTime AS [Timestamp], 
        MY_AS.ExpirationDate
    FROM
        [database].[dbo].[RF_CustomerCard] AS RF_CC
    INNER JOIN 
        [database].[dbo].[MY_ApplicationStatus] AS RF_AS ON MY_CC.CustomerID = MY_AS.CustomerID
    WHERE 
        MY_CC.CustomerID BETWEEN @startParam AND @endParam;
GO;

I get an error:

Syntax Error by SELECT MY_CC

So do you want a range (start and end) or do you want a list of IDs — Charlieface
– Charlieface, Commented Feb 17, 2021 at 2:27
i want to specify a start and end and get a list of all the records (not just ID's) in between the two specified paramters — GettingStarted
– GettingStarted, Commented Feb 17, 2021 at 2:37

Charlieface · Accepted Answer · 2021-02-17 02:58:07Z

2

You are best off creating an inline Table-Valued Function, they are much more performant than the style you have tried (Multi-Statement):

CREATE FUNCTION dbo.GET_CUSTOMER_WITH_ID
( @Id int )
RETURNS TABLE
WITH SCHEMABINDING -- better for performance, but blocks changes to underlying objects, forces two-part names
AS RETURN
(
SELECT
    MY_CC.CustomerID As CustomerID,
    CONCAT(MY_CC.FirstName, ' ', MY_CC.LastName) As Name,
    MY_CC.City,
    MY_CC.State,
    MY_CC.ZipCode,
    MY_CC.DateOfBirth,
    MY_AS.EventType As ApplicationStatus,
    MY_AS.EventDateTime As [Timestamp],
    MY_AS.ExpirationDate
FROM [dbo].[MY_CustomerCard] AS MY_CC
INNER JOIN [dbo].[MY_ApplicationStatus] AS MY_AS 
  ON MY_CC.CustomerID = MY_AS.CustomerID
WHERE MY_CC.CUSTOMERID = @id
)
;
GO

Now, instead of passing in a whole range, or a list, you can simply CROSS APPLY or OUTER APPLY this function to each outer row:

SELECT *
FROM @myIDRange ids
CROSS APPLY dbo.GET_CUSTOMER_WITH_ID ( ids.idValue) c;

SELECT *
FROM OtherList ids
CROSS APPLY dbo.GET_CUSTOMER_WITH_ID ( ids.idValue) c
WHERE ids.idValue BETWEEN @startParam AND @endParam;

You could also modify the function directly to supply those two parameters if that works better for you:

CREATE FUNCTION dbo.GET_CUSTOMER_WITH_ID
( @startParam int, @endParam int )
........
WHERE MY_CC.CUSTOMERID BETWEEN @startParam AND @endParam
)
;

GO

Do yourself a favour and don't use column names that need quotes []

edited Feb 17, 2021 at 2:58

answered Feb 17, 2021 at 2:36

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GettingStarted Over a year ago

I am getting a Syntax Error.

Charlieface Over a year ago

Sorry forgot AS RETURN (...)

Collectives™ on Stack Overflow

SQL Function with multiple parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related