January 20, 2015 at 10:03 am
Hi All, I want to dynamically filter a stored procedure with 2 parameters that contain a delimited list of values that can be fed into the IN clause of a stored procedure.
SELECT Answer
FROM dbo.TBL_ComplianceAnswers
WHERE (QueID IN (371, 373, 404)) AND (ServiceID IN (120, 65))
The stored procedure works fine with fixed values but I'd like the filtering values to be dynamically changed and to pass a delimited list of QueID values '371, 373, 404' and ServiceID values '120, 65' and for them to become parameters within the stored procedure suh as the following.
SELECT Answer
FROM dbo.TBL_ComplianceAnswers
WHERE (QueID IN (@QueID)) AND (ServiceID IN (@ServID))
Is this possible? does anyone have a bit of TSQL sample code I could play with.
Thanks,
Joe
January 20, 2015 at 10:12 am
Start here.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 20, 2015 at 11:08 am
Hi Michael, thank you for for the quick reply, I've had a look at the sample and created the function, but I can't see how to apply it to my situation.
Let's say I have a simple table with a list of 50 cities and I want to filter out London, New York, Paris how would I use that splitter in that situation, bearing in mind that I want to pass 'London', 'New York', 'Baltimore', 'Paris' as a single parameter.
SELECT City, Country, Population
FROM tblCities
WHERE (City IN ('London', 'New York', 'Baltimore', 'Paris'))
I'm new to TSQL so thanks for your patience.
Thanks
January 20, 2015 at 11:26 am
SELECT City, Country, Population
FROM tblCities
WHERE EXISTS (SELECT SP.item FROM dbo.DelimitedSplit8k('London,paris,new york',',') SP WHERE SP.item = tblCities.City)
OR
SELECT CTY.City, CTY.Country, CTY.Population
FROM tblCities CTY
INNER JOIN dbo.DelimitedSplit8k('London,paris,new york',',') SP ON CTY.City = SP.Item
The function DelimitedSplit8k is a table valued function. It returns a table so treat it like a table. Do a join, or an "if exists".
Are you a student? Because this kind of naming ("tblCities") will get you laughed at where I work.
What is the point of a three character prefix?
As a beginner, spend a day or two reading this series of articles.
http://www.sqlservercentral.com/stairway/
Then, post whatever questions you may have to the discussion forums.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 20, 2015 at 11:41 am
Michael, thank you so much for your patience and quick reply, greatly appreciated. I've now got it working with your help!!
I'm an Access developer looking to upsize to SQL Server. In the Access world of coding, prefixing of objects is known as the Leszynski naming convention it's a habit I'll have to break by the look of it. 🙂 I'll follow through on your suggestion to read through those articles.
Cheers,
January 21, 2015 at 3:48 am
Accept the two parameters as comma separated string.
Create a function that will split the comma separated string and returns table.
And use that function in where clause
January 21, 2015 at 5:34 am
Bhushan Kulkarni (1/21/2015)
Accept the two parameters as comma separated string.Create a function that will split the comma separated string and returns table.
And use that function in where clause
More or less what Michael L John recommends, except Michael links to an article which covers a string-splitter in some detail. Can you create a better one? Also, you suggest using the splitter function in the WHERE clause. Can you show how you might do this, and what benefits it might provide over the two complete solutions proposed by Michael?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 21, 2015 at 6:09 am
There is no better splitter function!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 21, 2015 at 6:32 am
I did come across this one on the stackoverflow website, I can't vouch for it's efficiency but it works just fine.
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
DECLARE @String VARCHAR(10)
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END
RETURN
END
GO
This is an example of its use...
declare @table table
(
rownum int,
csv nvarchar(300)
)
insert into @table values (1,'VALUE1, VALUE2, VALUE3')
insert into @table values (2,'VALUE1, VALUE2')
insert into @table values (3,'VALUE1, VALUE3')
insert into @table values (4,'VALUE2, VALUE3')
insert into @table values (5,'VALUE1, VALUE2, VALUE3')
insert into @table values (6,'VALUE3, VALUE1, VALUE2')
insert into @table values (7,'VALUE2, VALUE1')
declare @Filter nvarchar(50)
set @Filter = 'VALUE3,VALUE4'
select * from @table
SELECT * INTO #FilterTable FROM ufn_CSVToTable(@Filter, ',')
SELECT * FROM #FilterTable
select * from @table where EXISTS(SELECT String FROM #FilterTable WHERE csv like '%' + String + '%')
DROP TABLE #FilterTable
January 21, 2015 at 6:54 am
There are numerous splitter functions out there.
The example you provided uses a loop.
If you read the article, the performance of a loop splitter is significantly worse than Jeff Moden's splitter when the strings get longer.
For only a few elements, the performance is likely the same.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 21, 2015 at 7:09 am
SELECT *
FROM @table t
WHERE EXISTS (SELECT 1 FROM ufn_CSVToTable(@Filter, ',') s WHERE t.csv LIKE '%'+s.String+'%')
SELECT *
FROM @table t
WHERE EXISTS (
SELECT 1 FROM ufn_CSVToTable(@Filter, ',') s1
INNER JOIN ufn_CSVToTable(t.csv, ',') s2
ON LTRIM(s2.String) = s1.String)
-- splitter ufn_CSVToTable leaves leading space:
SELECT '['+string+']' FROM ufn_CSVToTable('VALUE3, VALUE1, VALUE2', ',')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 21, 2015 at 8:25 am
Michael is right - there are probably almost as many string splitter functions as there are questions about them. Some of them work better than others, but Jeff's DelimitedSplit8K is certainly the fastest one I've found that works on SQL 2005+. The first two links in my signature go to the Tally table article and the Tally Oh article, where the DelimitedSplit8K is explained and compared with other approaches to the very common problem of splitting strings.
But, don't take Michael's, mine or anyone else's word for it. Test it out yourself. I'd run it over a 1 million row table to start. You might not need to run it over something that big today, but you might in the future. In performance testing, you want to find and fix weakness. Running a test of 100 rows should work great, but 1M rows will probably expose weakness. Who knows? You might even find ways to improve it. 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply