CODEWITHSUNDEEP
sql-serverfunctionparameters
GettingStarted
GettingStarted

Reputation: 7605

SQL Function with multiple parameters

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

Upvotes: 0

Views: 1817

Answers (1)

Charlieface
Charlieface

Reputation: 71178

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 []

Upvotes: 2

Related Questions