August 10, 2010 at 11:01 am
The definitive answer for passing a list as a parameter is:
August 10, 2010 at 11:35 am
unfortunately, I have both xml and looping to handle this situation. I tend to lean towards the xml because of the flexibility and descriptiveness. The flexibility is that we can pass several different parameters in as well as several values of the same parameter. At the same time, we(my shop) has a looping function that returns a result set for the SRS situation. That function is used like this:
SELECT *
FROM xyz
WHERE (@par IS NULL OR xyz_id IN (SELECT entry FROM udf_split(@par, ',')))
AND (@par2 IS NULL OR abc_id IN (SELECT entry FROM udf_split(@par2, ',')))
I was quite frustrated once I saw the looping because I had pointed the developer at Jeff's tally table article but by that time it was to late. Already in production.
Below is the xml snippet we continue to copy and modify for each situation.
CREATE PROCEDURE [dbo].[usp_partBomADD]
@part_id INT
, @xmlPartBom VARCHAR(1000)
AS
BEGIN
-- our xml document
DECLARE @doc VARCHAR(1000);
SET @doc = '<root>
<parameter part_id="1" description="my part" usage="1"/>
<parameter part_id="2" description="my part" usage="1"/>
</root>';
-- xml table handle
DECLARE @hdoc INT;
--declare table variable to hold our data
DECLARE @tblXML TABLE (
id INT IDENTITY(1,1)
, part_id INT
, description VARCHAR(50)
, usage INT
)
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
-- query the xml for data
INSERT @tblXML
SELECT part_id, description, usage
FROM OPENXML (@hdoc, 'root/parameter')
WITH (part_id INT, description VARCHAR(50), usage INT);
-- Remove the internal representation.
EXEC sp_xml_removedocument @hdoc
--SELECT * FROM @tblXML
END
August 10, 2010 at 12:00 pm
Personally, I would lean towards a tally table approach, but I can see where the simplicity of this approach would be favorable to some of my non-sql-I-don't-want-to-touch-it-unless-I-have-to developers.
What do you guys think of checking for invalid entries with this?
If @@RowCount <> (LEN(REPLACE('as,df,sa,as,',',',''))/2)
Begin
...
End
August 10, 2010 at 12:47 pm
Jeff Moden (8/10/2010)
TheSQLGuru (8/10/2010)
Didn't we have a MASSIVE thread here on SSC.com on string parsing, with amazing code samples and wonderful benchmarks??We did. I tried to voice an objection about using all the same data for all the rows in any given test table (delimiters ALL in the same position causes lightning speed due to the way things are cached) but no one listened. All is not as it seems on that thread because of the test data that was used. As a result, I'd recommend retesting any code found in that thread before you use it.
Where was that thread?
I have seen a few here and there (one was a QOTD).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 10, 2010 at 12:48 pm
Thanks for the article.
I think one must be careful about the string parsing method used. I don't believe there is a one size fits all method for all scenarios. I have used a different version here and there depending on the needs (one was CTE based, and another uses the XML)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 10, 2010 at 1:34 pm
August 10, 2010 at 2:05 pm
In sql2005 in my application we have lists of primary keys passed to functions so we can't use a lookup table approach as in the article. I've seen parse routines before but didn't like their chopping method so I rewrote it to handle any delimiter and null values and using pointers.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.StringParse') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.StringParse
GO
CREATE FUNCTION dbo.StringParse(
@STR varchar(max),
@delimiter varchar(20),
@emptytonull bit)
returns @tokens TABLE (rownbr int PRIMARY KEY NOT NULL, token varchar(max) NULL)
AS
-- written BY Bill Talada
BEGIN
/*
split a string INTO tokens AND RETURN them IN a TABLE.
empty string returns one NULL row
a string WITH only a delimiter returns two NULL rows, one before it AND one after it.
a delimiter at the beginning means a NULL value appears before it.
two delimiters IN a row implies a NULL value BETWEEN them.
a delimiter at the END OF string implies a NULL value after it.
*/
DECLARE
@delimiterlength int,
@currptr int,
@nextptr int,
@token varchar(max),
@rownbr int
SET @delimiterlength = datalength(@delimiter)
SET @rownbr = 0
SET @currptr = 1 - @delimiterlength
SET @nextptr = charindex(@delimiter, @STR, @currptr+@delimiterlength)
--SELECT @currptr, @nextptr, @delimiterlength
WHILE @nextptr > 0
BEGIN
SET @rownbr = @rownbr + 1
SET @token = substring(@str, @currptr+@delimiterlength, @nextptr - (@currptr+@delimiterlength))
IF len(@token) = 0
BEGIN
IF @emptytonull=1
SET @token = NULL
ELSE
SET @token = ''
END
INSERT INTO @tokens VALUES (@rownbr, @token)
SET @currptr=@nextptr
SET @nextptr=charindex(@delimiter, @STR, @currptr+@delimiterlength)
END
-- last row
SET @rownbr = @rownbr + 1
SET @token = substring(@str, @currptr + @delimiterlength, datalength(@str) - @currptr + @delimiterlength)
IF len(@token) = 0
BEGIN
IF @emptytonull=1
SET @token = NULL
ELSE
SET @token = ''
END
INSERT INTO @tokens VALUES (@rownbr, @token)
RETURN
END
go
August 10, 2010 at 2:20 pm
Short of SS 2008 my favorite is a table valued function... I've used this quite a bit and it never fails me, except for the < character which is invalid in XML. A couple lines of code within the function cleans that up nicely as well.
Create FUNCTION [dbo].[UDF_TBL_ConvertStringToRows](@Data Varchar(Max), @Delimiter Varchar(10))
Returns @DataTable Table (
Id Int Identity(1,1),
Data Varchar(Max))
As
Begin
Declare @TextXML XML;
Select @TextXML = Cast('<Root><Row>' + Replace(@Data, @Delimiter, '</Row><Row>') + '</Row></Root>' As XML);
Insert @DataTable(Data)
Select X.a.value('.','Varchar(Max)') As Data
From @TextXML.nodes('/Root/Row') X(a)
Return
End
--Test the Function
Declare @Parm varchar(max)
Select@Parm = '1,2,3,45,1001,2001'
SelectId, Data from dbo.UDF_TBL_ConvertStringToRows(@Parm,',')
Select@Parm = '"Data test" | Code Test'
SelectId, Data from dbo.UDF_TBL_ConvertStringToRows(@Parm,'|')
HTH
RJ
August 10, 2010 at 2:40 pm
I know XML in SQL Server 2008 has been greatly enhanced, but the question is me and a lot of people I know are still working on SQL Server 2000. I work in a big enterprise that don't change quickly, and our developments for the last eight? ten? years have been on this old but still valid version (this year I know they are deploying some SQL 2005, so maybe soon we'll have to change our mind...)
August 10, 2010 at 2:54 pm
steveplate (8/10/2010)
I wonder how this performs if the table you are searching is 20K+ rows. It seems that this might be faster only for really small tables such as states. Has anyone done any benchmarks?
Hi steveplate!
Thanks for your question.
Sure it won't be very quick as we have no index gain using a function. The performance, being a question sometimes, it's not a point here: we needed to serve a multivalued API to our customers to avoid them querying their SP for every needed value. The chopping string loop was also valid but, in my opinion, the table method is more elegant and simpler.
August 10, 2010 at 2:57 pm
While I don't have a SS2000 test bed, I'm fairly certain XMl was in 2000. The code I posted works on 2005 for certain.
August 10, 2010 at 3:02 pm
jcboyer-1091017 (8/10/2010)
I have been using an XML string to achieve the same results. Not sure that it is faster, but it is quite flexible as:EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLParameters
insert into #T
select *
FROM OPENXML (@idoc, '/OrderGroup/Item', 1)
WITH #T
EXEC sp_xml_removedocument @idoc
In this example #T is a large table with over 20 columns. @XMLParameters is varchar(max) to pass as many rows as necessary at once, which is and added bonus, because there is still no Table as parameter. (I am using varchar instead of XML because XML is more strict and we do not need this here.)
Comments on this approach are welcome.
Jerome
Hello Jerome!
As I have stated in others posts, XML was not an option for me (SQL2K)
August 10, 2010 at 3:06 pm
RJ Roberts (8/10/2010)
While I don't have a SS2000 test bed, I'm fairly certain XMl was in 2000. The code I posted works on 2005 for certain.
Yes, but as far as I remember it was only the extension of SELECT ... FOR XML
August 10, 2010 at 3:38 pm
john.moreno (8/10/2010)
The definitive answer for passing a list as a parameter is:
I'm a devote from Erland's work, and it makes me happy to admit I personally had his help once I needed it (a lot).
That said, if you go to the link you mention, you'll see that does not refer to a method such as that presented here.
Maybe it is by it's lack of generality (it doesn't work for free text parameters, only those in the reference table)... or simply he didn't thought of... 🙂
August 10, 2010 at 3:52 pm
janusz pazgier (8/10/2010)
Nice article to provide help for parsing delimited values, but I like better XML version provided by some user in the comments which is simpler but maybe more resource consuming.But my question is why this article has title :parsing stored procedure parameters if you just looking for specific switch value in single parameter? You are not looking for pair Name=Value, just for values. Could you provide example how you use values received from your parsing process? Because maybe all your work is not really required if you next do something based on your parsed values using for example CASE statement with fixed values and you have to do word search again later in he code.
Could you provide example of usage of parameter values received from your parse routine?
Hi janusz!
The key of the usage: let's say you have a string as 'WA,CA,TX' (they would be spanish provinces, in my case 🙂 ), and with this method you get a temp table with the 3 values one-for-row.
With this table, you can filter the main data table using an INNER JOIN:
SELECT <some_data>
FROM main_data_table AS a
INNER JOIN #parameter_constructed_table AS b
ON a.state = b.state
You could also get the string directly in the WHERE clause but it only works with dynamic SQL (and you know it's dangerous rely on it, as it's prone to syntactic errors and possible SQL injections):
Declare @param varchar(20)
Set @param = 'WA,CA,TX'
Declare @sql varchar(8000)
Set @sql = '
SELECT <some data>
FROM main_data_table
WHERE state IN ( ''' + @param + ''' ) '
EXEC (@sql)
Viewing 15 posts - 31 through 45 (of 124 total)
You must be logged in to reply to this topic. Login to reply