CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
RKN
RKN

Reputation: 962

Pass multiple parameters to sql function

I have sql table with data as below 

SnackID        Name
   1         Chicken
   2         Soda
   3         Chocolate 
   4         IceCream

I have the below user-defined function which accepts arguments as a string with multiple values like 'Chicken', 'Soda'.

CREATE FUNCTION GetSnackCodes
(
  @myValues varchar(max)
)
RETURNS @SnacksCodes TABLE 
(
       mySnackCoded int NULL
)
AS
BEGIN 

 insert into @SnacksCodes 
 select SnackID  from Snack where Name In (@myValues)

return ;
END;
GO

When I tried to invoke this function by passing multiple values to this variable I am not getting expected result. I guess it's trying to search multiple (comma separated )values as a single value.

Any other possible workaround for how to pass this values?

Upvotes: 2

Views: 5226

Answers (2)

Rajesh Aithal
Rajesh Aithal

Reputation: 21

you can split comma separated value and store that in temporary table then pass that table value result to query

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Your guess is correct - when you pass a single string with comma-separated values, SQL server treats it as a single string value.

Use table valued parameter instead:

CREATE TYPE SnackCode As Table (
    Name NVARCHAR(50)
);
GO;
CREATE FUNCTION GetSnackCodes (
  @myValues SnackCode
)
RETURNS @SnacksCodes TABLE (
       mySnackCoded int NULL
)
AS
BEGIN 
    insert into @SnacksCodes 
        select SnackID
        from Snack
        where Name In (select Name from @myValues)
return ;
END;
GO

Upvotes: 4

Related Questions