Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Parsing Parameters in a Stored Procedure Expand / Collapse
Author
Message
Posted Monday, August 09, 2010 9:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
Comments posted to this topic are about the item Parsing Parameters in a Stored Procedure
Post #966348
Posted Monday, August 09, 2010 11:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:38 AM
Points: 39, Visits: 875
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.


Post #966368
Posted Tuesday, August 10, 2010 12:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 3:42 PM
Points: 2, Visits: 35
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
Post #966377
Posted Tuesday, August 10, 2010 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:52 AM
Points: 5, 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
Post #966380
Posted Tuesday, August 10, 2010 12:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 404, Visits: 590
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)




Post #966385
Posted Tuesday, August 10, 2010 2:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 07, 2013 2:54 AM
Points: 317, Visits: 94
Very simple and useful.
I was looking for something like this only, and it helps me a lot.

Thanks.
Post #966446
Posted Tuesday, August 10, 2010 2:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:28 AM
Points: 237, Visits: 121
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
Post #966454
Posted Tuesday, August 10, 2010 3:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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
Post #966473
Posted Tuesday, August 10, 2010 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 12:24 PM
Points: 1, 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?
Post #966575
Posted Tuesday, August 10, 2010 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 03, 2014 4:39 AM
Points: 6, Visits: 49
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
Post #966588
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse