Stored procedure's where clause parameters

Question

I have the below scenario:

----------------
ID    |    Type
----------------
1     |    AB
2     |    CD
3     |    EF

Now, the query I need for this query in stored procedure is:

SELECT * FROM Table_Name WHERE TYPE IN ('AB','CD','EF')

I have written the below procedure:

CREATE PROCEDURE dbo.usp_get_all
  @Type    VARCHAR(200)
AS
BEGIN
  SELECT * FROM Table_Name WHERE TYPE IN (@Type)
END

This query is working fine only when I am calling it in the below way:

EXEC usp_get_all 'AB'

Now, there may be a situation like below, which is not working

EXEC usp_get_all '''AB'',''CD'',''EF'''

My question:

I don't want to use any function to split the comma separated input parameters. Only by using the input parameter I want to get the same result like:

SELECT * FROM Table_Name WHERE TYPE IN ('AB','CD','EF')

Is there any way that can do the trick? Can I use dynamic SQL? If I can, how can I use that in this case?

somethin like this? http://stackoverflow.com/questions/15024219/conversion-failed-in-cursor-in-stored-procedure/15038078#15038078 — bummi, Mar 05 '13 at 11:31
Do you understand that there is a logical difference between a single string parameter that may happen to contain `'` and `,` characters, and multiple string parameters, separated by commas? — Damien_The_Unbeliever, Mar 05 '13 at 11:37

score 1 · Accepted Answer · edited Mar 05 '13 at 12:07

1

You can use:

CREATE PROCEDURE myProc (@typy NVARCHAR(299))
AS

DECLARE @SQL NVARCHAR(4000)
SET @SQL='SELECT * from table_name where typ in ('+@typy+')'
EXEC(@SQL)

And then execute:

DECLARE @return_value int
EXEC    @return_value = [myProc]
        @typy = N'''AB'',''BC'''

edited Mar 05 '13 at 12:07

Perception

answered Mar 05 '13 at 11:48

Marcin Pazgier

1 Answers1