csv list of elements as parameter for stored procedure
A while ago, Tony Rogerson showed a way how to pass a list of integers (csv) to stored procedure. Approach of creating a script and executing it is OK for smaller amounts of data. I thought, that maybe, as xml is a form of text after all, it would be more appropriate? I crafted a stored proc based on Tony's code that instead of generating a script, generates an xml stream that is subsequently used in query inserting rows. This method is also resistant to sql injection attemtpts so such stored proc is not a 'bobby tables' one :)
Here's the code of the procedure:
create
table tStoredInts (afield int)
go
create
proc csv_to_table2
@csv
varchar(max)
AS
BEGIN
/***
Insert numbers from a CSV to tStoredInts table
***/
declare @handle int --xml document handle
SET @csv = ltrim(rtrim(@csv))
IF RIGHT( @csv, 1 ) = ',' -- If last character is a comma remove it it
SET @csv = left(@csv, len(@csv) -1)
DECLARE @xml varchar(max)
SET @xml = REPLACE( @csv, ',', '</o><o>')
SET @xml = '<numbers><o>' + @xml + '</o></numbers>'
exec sp_xml_preparedocument @handle output, @xml
--insert elements from xml
insert tStoredInts (afield)
select field
from openxml(@handle, '/numbers/o', 2) with (field int '.')
exec sp_xml_removedocument @handle
end
--proc