Sending multi-values in one param to sp 3

[jlsmithhartfiel]

Hi! Would normally search for this...

I have a stored procedure where one of the parameters is used in an IN clause. I want to pass a comma delimited list to the sp. Like:

exec sp_test "'val1','val2','val3'"

CREATE PROCEDURE sp_test
@proptype varchar(100)as
select p.uid,
from prp p
where (@proptype is null 
   or (@proptype is not null 
  and p.proptype in (@proptype)))

It seems this cannot be done. What I'm looking for is the most time efficient alternative.

Thanks in advance! Jessica

 

[tlbroadbent]

You can create and execute a dynamic SQL statement or if you run SQL Server 2000, you can use a function such as fn_split to parse the string.

Dynamic SQL:
CREATE PROCEDURE sp_test
@proptype varchar(100) as

Declare @sql varchr(400)

Set @sql= 'select p.uid from prp p
where (@proptype is null
or (@proptype is not null
and p.proptype in (' + @proptype + ')))'

Exec(@sql)

Read about fn_split and download the code from this link.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/TreatYourself.asp

I prefer the fn_split solution over dynamic SQL in SQL 2000. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[osjohnm]

Hi Jessica

I had to do this last week and I found an answer here:

http://accesshelp.net/content/view.aspx?_@id=53446

It worked perfectly for my procedure.

Hope this helps

John

 

[jlsmithhartfiel]

Thanks to both of you for the quick response!

This is a great help to me! Jessica

 

[jlsmithhartfiel]

I ended up using a combination of both to get what I wanted...

I used the fn_Split, Thank goodness for people who share their code!!!!!!!!!!!!!!!!!!!!!!!!

I also needed to take into account if the user didn't send any id's through, so this is what I ended up with:

CREATE PROCEDURE sp_test
@proptype varchar(100) as
select p.uid,
       p.proptype
from prp p
where @proptype is null or 
(@proptype is not null and p.proptype in (select value from dbo.fn_Split(@proptype,',')))
return
GO

Long live Tek-Tips! Jessica