2

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?

Hussein Khalil
  • 1,395
  • 11
  • 29
Roshan
  • 873
  • 12
  • 33

2 Answers2

4

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
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
2

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

Rajesh Aithal
  • 21
  • 1
  • 6