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?
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;
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.
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)) + '%'
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.
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.
