SQLTeam.com | Weblogs | Forums

Create Dynamic SQL to handle multiple values of a parameter


#1

Hello SQL Expert,

I am trying to create a dynamic SQL from below simple query so it can handle end user multiple values of a parameter called job ID which is a SUBSTRING from JobID column.

So here is my simple query

Declare @Job varchar(max)
Set @Job = '216'

Select JobID
,Inventory
,InvoiceNumber
From [sales_tbl]
Where substring(JobNo,1,3) in (@Job)

Example of the JobID values are: 216AADD5, 364EED8, 654TTRD0, and so on. So, we are using the first 3 digit as our JobNo parameter.

With the current above query, end user only able to run one value at one execution and we are trying to let the end users to select more than one values like for example: 216,364 and 654 and so on. I know using the Dynamic SQL will take of this but I am not sure how to do it. Anyone?


#2

Something like this perhaps:

declare @jobids nvarchar(100)='216,364,654';
declare @sqlstring nvarchar(1000);

set @sqlstring='
select jobin
      ,inventory
      ,invoicenumber
  from sales_tbl
 where left(b.jobno,3) in ('''+replace(@jobids,',',''',''')+''')
';
print @sqlstring; -- first see if query looks correct before executing
--exec sp_executesql @sqlstring;

#3
  1. Split the multi-value input string into a table. There are many such functions that can do that. The best performing is likely DelimitedSplit8K, so I will use it. You can Google it to get its source.

  2. Use LIKE 'nnn%' rather than SUBSTRING, because SQL could use an index seek to satisfy the LIKE comparison but not the SUBSTRING (in tech terms, LIKE 'nnn%' is "sargable", SUBSTRING(...) is not).

    Declare @Jobs varchar(8000)
    Set @Jobs = '216,364,654'

    Select st.JobID
    ,st.Inventory
    ,st.InvoiceNumber
    From [sales_tbl] st
    Cross Apply dbo.DelimitedSplit8K ( @Jobs, ',' ) ds
    Where st.JobNo LIKE LTRIM(RTRIM(ds.Item)) + '%'


#4

Thank you Bitsmed, this works!!! Dynamic SQL is very interesting!!! Thanks Bitsmed


#5

Hi Scott, is dbo.DelimitedSplit8K a free function in SQL? I tried and got an invalid error. I am just wondering if this is something we need to build in.


#6

Yes, it's free. It's not from MS/SQL, but it's easy to Google to get the source. I can't post it for you, or I would, but my filter at work blocks any code with UNION ALL in it as a SQL injection attempt (!) and I get blocked from posting it.


#7

Quick search for DelimitedSplit8k found http://www.sqlservercentral.com/articles/Tally+Table/72993/
Please read the article (which has the code) as Jeff did a great job.


#8

In case you are not aware of it: Dynamic SQL has significant security risks associated with it, if not done correctly :slightly_smiling:


#9

Thank you all..!!