SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parsing Parameters in a Stored Procedure


Parsing Parameters in a Stored Procedure

Author
Message
dbuendiab
dbuendiab
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 218
Comments posted to this topic are about the item Parsing Parameters in a Stored Procedure
tskelley
tskelley
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 1185
Nice coverage. Thank you for taking the time to spell out the steps and issues when working with charindex in this manner. We do something similar for handling delimited values stored directly in the db field. And yes, when pulling from a known lookup list, it's nice and simple to append your delimiter on both ends. Love these brute force, simplistic solutions.



mr.mcgoo
mr.mcgoo
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 59
This comes in very handy with Reporting Services (SSRS) when dealing with it's Multi-Valued parameters.

I have used a routine like this heavily for this very purpose for some time now. I have produced a function tailored for use with reporting services parameters but is relevant to any scenario where you are dealing with an array of id's passed in one parameter or field.

I can't take credit for the guts of the procedure as much of it was taken from articles like this. But I include it here because it takes this articles point further and provides a relatively elegant solution to a fairly ugly problem;

Create Function General.ufn_SplitIntegers
(@String varchar(Max))
Returns @Results Table (Value Integer Primary Key)
As
-- ------------------------------------------------------------------------------------------------
-- This function takes a string containing an array and splits the array out and returns it as a
-- table object. This is particularly useful for handling multi value parameters in Reporting
-- Services.
--
-- There are variants of this routine. This one is tuned to assume a delimiter of comma and that
-- all values passed are integers. Others allow for user defined delimiter and dealing with text values.
--
-- To use it code as follows;
-- SELECT Value FROM General.ufn_SplitIntegers('3,23,45,2,6')
-- or
-- SELECT Value FROM General.ufn_SplitIntegers(@Parameter)
-- ------------------------------------------------------------------------------------------------
Begin
Declare @Index Int, @Slice varchar(Max), @Delimiter char(1)

Select @Index = 1, @Delimiter = ','

If @String Is Null Return

While @Index != 0
Begin
-- Get the Index of the first occurence of the Split character
Select @Index = CharIndex(@Delimiter, @String)

-- Now push everything to the left of it into the slice variable
If @Index !=0
Select @Slice = Left(@String, @Index - 1)
Else
Select @Slice = @String

-- Put the item into the results set
Insert Into @Results(Value) Values(@Slice)

-- Chop the item removed off the main string
Select @String = Right(@String, Len(@String) - @Index)

-- Break out if we are done
If Len(@String) = 0 Break
End

Return
End
GO
david.skov
david.skov
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 100
Nice solution, I was actually looking for a better way to parse parameter values from reporting service (multi-valued) over to a procedure. And until now I only had the loop-function. This is a nice way and probably faster way to parse values.

Tx
Mikael Eriksson SE
Mikael Eriksson SE
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 994
The XML Version


declare @Param as xml
set @Param = '<I>1</I><I>2</I><I>3</I>'

select IntList.IntField.value('.', 'int')
from @Param.nodes('I') IntList(IntField)





yogender
yogender
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 131
Very simple and useful.
I was looking for something like this only, and it helps me a lot.

Thanks.:-)
wim.buyens
wim.buyens
SSC-Addicted
SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)SSC-Addicted (409 reputation)

Group: General Forum Members
Points: 409 Visits: 188
The use of table-valued parameters in SQL Server 2008 removes the need for these comma separated parameters.

But if you need something like this you could use a CLR function or use something like this:

--select * from [System].[tfn_DelimStringToTable]('a,b,c,d,e',',')
CREATE FUNCTION [System].[tfn_DelimStringToTable](@str NVARCHAR(max),@delim CHAR(1))
RETURNS @table TABLE ([ID] INT IDENTITY(1,1),[Column] sysname NOT NULL)
WITH SCHEMABINDING,ENCRYPTION
AS
BEGIN
DECLARE @x XML
SET @x = '<t>' + REPLACE(@str,@delim, '</t><t>') + '</t>'

INSERT INTO @Table([Column])
SELECT x.i.value('.', 'NVARCHAR(MAX)') AS token
FROM @x.nodes ('//t') x(i)

RETURN
END
mohd.nizamuddin
mohd.nizamuddin
Mr or Mrs. 500
Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)Mr or Mrs. 500 (514 reputation)

Group: General Forum Members
Points: 514 Visits: 198
Nice article and I think better approach.

I have solved the same problem but in different way. Please have a look.

http://www.sqlservercentral.com/articles/Comma+seperated/67417/

Thanks,
Nizam
steveplate
steveplate
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
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?
jcboyer-1091017
jcboyer-1091017
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 61
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search