How to pass 'IN' parameter by using Sql Server Managment Studio execute window?

Question

This is my sample stored procedure query.

CREATE PROCEDURE EMP_Details
                    @EmpType varchar(6), 
                    @Location varchar(55)
AS
BEGIN
    SET NOCOUNT ON;
SELECT EmpName,EmpAge,EmpOrgCode FROM Employee WHERE EmpType = @EmpType AND Locatoin IN (@Location)

END

When I am try to execute it via SSMS.I want to pass 'IN' parameter into SMSS query executor.What is the correct way to pass values into 'IN' parameter('@Location')

 DECLARE    @return_value int
    EXEC    @return_value = EMP_Details
            @EmpType  = N'FUL',
            @Location = N'CY,CA,NY,WA' -- <<this is not work for me

    SELECT  'Return Value' = @return_value   

More explanation.

This is my table.

Image - 1.0

This is my real question.(How to add 'IN' values from this window correctly ?)

Image - 2.0

After that SSMS create this query.

USE [test]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[EMP_Details]
        @EmpType = N'A',
        @Location = N'CY,NY'

SELECT  'Return Value' = @return_value

GO

But above query Not working

Then manually run this query (Image - 3.0).

Image - 3.0

I want to pass the parameter like highlight text (Image 3.0) by using SQL server management studio query execute window (above window - Image 2.0)

Any one who try to close this question,that buggers must have to comment with the clear reason.

Answer 1

Another approach is using a @Locations xml parameter, this wait you avoid using functions,see below

  EXEC    @return_value = EMP_Details
        @EmpType  = N'FUL',
        @Locations = N'<Locations><Location Name="CY"/><Location Name="CA"/><Location Name="CN"/></Locations>' 


 -----Store Procedure--------------
 CREATE PROCEDURE EMP_Details(
                @EmpType varchar(6), 
                @Locations xml
                )
AS
  BEGIN
   SET NOCOUNT ON;
   SELECT EmpName,EmpAge,EmpOrgCode FROM Employee WHERE EmpType = @EmpType 
   AND Locatoin IN (SELECT Locations.Node.value('@Name', 'VARCHAR(50)') 
   FROM @Locations.nodes('/Locations/Location') Locations(Node))
END

Answer 2

You could create function to split values ant then run subquery:

CREATE FUNCTION dbo.SplitString(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') 
         AS nvarchar(max)) AS stval, 
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END;

CREATE PROCEDURE dbo.EMP_Details
                    @EmpType varchar(6), 
                    @Location varchar(55)
AS
BEGIN
SELECT EmpName,EmpAge,EmpOrgCode FROM Employee 
   WHERE EmpType = @EmpType AND Locatoin IN 
   (SELECT val FROM dbo.SplitString(@location, ','))
END;

Answer 3

This is how I have done. It may not be most effiect way to acheive the desired result.

1. It create creates a temp table from CSV.

Script

CREATE FUNCTION [ConvertCSVToTbl] ( @list NVARCHAR(MAX) )
RETURNS @tbl TABLE ( ID INT NOT NULL )
AS 
    BEGIN
        DECLARE @pos INT ,
            @nextpos INT ,
            @valuelen INT

        SELECT  @pos = 0 ,
                @nextpos = 1

        WHILE @nextpos > 0 
            BEGIN
                SELECT  @nextpos = CHARINDEX(',', @list, @pos + 1)
                SELECT  @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
                                         ELSE LEN(@list) + 1
                                    END - @pos - 1
                INSERT  @tbl(ID)
                VALUES  (CONVERT(INT, SUBSTRING(@list, @pos + 1, @valuelen)))
                SELECT  @pos = @nextpos
            END
        RETURN
    END
GO

2. Following function will return 1 if found else 0

Script

CREATE FUNCTION [SearchIfNotEmpty]
    (
      @list NVARCHAR(MAX) ,
      @ID INT
    )
RETURNS BIT
AS 
    BEGIN 
        IF ( @list IS NULL ) 
            RETURN 1 

        IF EXISTS ( SELECT  *
                    FROM    [ConvertCSVToTbl](@list)
                    WHERE   id = @ID ) 
            BEGIN 
                RETURN 1 
            END 

        RETURN 0 
    END 
GO

Then you can use it like

SELECT * WHERE [Searchifnotempty](@Location, Locatoin) = 1