Can I pass in several strings to one parameter in Dynamic SQL?

Question

I am trying to pass several strings into one parameter called '@Portfolio', and use the LIKE clause. Is that possible? Something like this.

Declare @AsOfDate varchar(10)
Declare @PID varchar(5)
Declare @Portfolio varchar(20)
Declare @sqlCommand  varchar(max)
Set @AsOfDate = '04/30/2018'
Set @Portfolio = 'Treasury Investment,CASH,Derivatives,Wholesale Funding'
Set @PID = 'I.A.6'

...etc...

WHERE ((Tgt_DO.PID like ' + '''' + @PID + '''' + ' AND Tgt_DO.Portfolio like ' + '''' + @Portfolio + '''' + ' And Tgt_DO.AsOfDate = ' + '''' + @AsOfDate + '''' + '))'

I am on SQL Server 2008.

you can build your query using `IN` if you would like to see the one result — LONG, May 24 '18 at 17:44
This is not dynamic SQL. You are just concatenating values in a static way. — The Impaler, May 24 '18 at 17:44
If you had your parameters on a list or array you could build a SQL statement using a while statment or similar to produce an `IN (...)`. — The Impaler, May 24 '18 at 17:45
@ryguy72, like "TheI mpaler" said, that is not a dynamic sql, ignore it if you have already noticed that xD — LONG, May 24 '18 at 17:48

score 1 · Accepted Answer · answered May 24 '18 at 18:44

To elaborate on the comments... since you use LIKE in some cases you'll need to concate the %

where
    Tgt_DO.PID like '%' + @PID + '%'
    and Tgt_DO.Portfolio like '%' + @Portfolio + '%'
    and Tgt_DO.AsOfDate = @AsOfDate

This could bring back unintended results since @Portfolio is a comma separated string. I would use a Table-Valued Parameter or split them into a temp table or table variable using a splitter, like this answer

Can I pass in several strings to one parameter in Dynamic SQL?

1 Answers1