Trouble using created function in ms sql

Question

I've made a SQL function. This is a simple function which gets IDs of given Gateway and Category objects by name:

USE [dev_ticketsystem]
GO

CREATE FUNCTION dbo.GetGatewayCategoryPair(@GatewayName varchar(100), @CategoryName varchar(100))

RETURNS @PairedTable TABLE 
(
    GatewayId uniqueidentifier,
    CategoryId uniqueidentifier
)
AS
BEGIN
    DECLARE @CategoryId uniqueidentifier;
    DECLARE @GatewayId uniqueidentifier;

    SELECT @CategoryId = [Id] 
    FROM [dev_ticketsystem].[dbo].[Category]
    WHERE [Name] = @CategoryName;


    SELECT @GatewayId = [Id] 
    FROM [dev_ticketsystem].[dbo].[Gateway]
    WHERE [Name] = @GatewayName;

    IF @GatewayId IS NOT NULL 
    BEGIN
        IF @CategoryId IS NOT NULL
        BEGIN
            INSERT @PairedTable
            SELECT @GatewayId, @CategoryId;
        END;
    END;
    RETURN;
END;
GO

After executing this script it created the function, so I can see in MS SQL Management Studio.

But when I'm trying to call it with :

USE [dev_ticketsystem]
GO

INSERT INTO [dbo].[CategoryGateway]
           ([GatewayId]
           ,[CategoryId])
     VALUES
           (dbo.GetGatewayCategoryPair('GateName', 'CatName').GatewayId,
           dbo.GetGatewayCategoryPair('GateName', 'CatName').CategoryId);

It throws exception :

Incorrect syntax near 'GetGatewayCategoryPair'.

How could this be fixed?

Insert into CategoryGateWay (GatewayId, CategoryId) SELECT GatewayId, CategoryIdfROM dbo.GetGatewayCategoryPair('GateName', 'CatName') — Rajat Jaiswal
– Rajat Jaiswal, Commented May 23, 2018 at 10:53

Gordon Linoff · Accepted Answer · 2018-05-23 10:53:07Z

1

You have declared the function to return a table. Hence, you need to refer to it in the FROM clause:

INSERT INTO dbo.CategoryGateway(GatewayId, CategoryId)
     SELECT gcp.GatewayId, gcp.CategoryId
     FROM dbo.GetGatewayCategoryPair('GateName', 'CatName') gcp;

answered May 23, 2018 at 10:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

KamikyIT Over a year ago

Works perfectly! Thank you very much.

Thom A · Accepted Answer · 2018-05-23 11:10:34Z

Personally, I would move away from a Multi-statement table value function; they can be very slow. Use a inline table-value function. Which should look something like this:

CREATE FUNCTION dbo.GetGatewayCategoryPair_itvf (@GatewayName varchar(100), @CategoryName varchar(100))
RETURNS TABLE
AS RETURN

    SELECT G.ID AS GatewayId,
           C.ID AS CategoryId
    FROM (SELECT [Id] 
          FROM [dev_ticketsystem].[dbo].[Category]
          WHERE [Name] = @CategoryName) C
    CROSS APPLY (SELECT [Id] 
                 FROM [dev_ticketsystem].[dbo].[Gateway]
                 WHERE [Name] = @GatewayName) G
   WHERE C.ID IS NOT NULL
     AND G.ID IS NOT NULL;

Collectives™ on Stack Overflow

Trouble using created function in ms sql

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related