18 March 2007 15:42
tonyrogerson
Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation
If you are splitting a CSV that just contains numbers, for instance surrogate key id’s that you are passing in because of a multiple select checkbox or something and you know the values range then why split the string? It’s simpler than that – no splitting required, just use dynamic SQL and an IN coupled with a numbers table...
Create the numbers table, note – if you are just passing in the surrogate key id of say your individual table then you don’t even need a numbers table – just use the id column on the individual table...
create table numbers (
number int not null primary key clustered
)
declare @i int
set @i = 1
while @i <= 32767
begin
insert numbers ( number ) values( @i )
set @i = @i + 1
end
go
create proc csv_to_set
@csv varchar(max)
as
begin
set @csv = replace( @csv, '''', '''''' )
if object_id( 'tempdb..#csvset' ) is null
begin
print '#csvset must already exist before this proc is called'
-- create table #csvset (
-- csv_value smallint not null
-- )
end
else
begin
insert #csvset ( csv_value )
exec( '
select number
from numbers
where number in ( ' + @csv + ' )
' )
if @@rowcount <> ( LEN( @csv + ',' ) - LEN( REPLACE( @csv + ',', ',', '' ) ) )
begin
print cast( ( LEN( @csv + ',' ) - LEN( REPLACE( @csv + ',', ',', '' ) ) ) as varchar(50) )
raiserror( 'A value exists in the CSV that is not in the numbers table', 16, 1 )
truncate table #csvset
return
end
end
end
The procedure is run using the example below, unfortunately because you can’t put dynamic SQL in a function then you can only do this in a stored procedure (a table valued function would remove the need for the # table).
I’ve also put a check in there that compares the @@ROWCOUNT (inserted rows) against how many values are in the CSV, if there is a mismatch then an error is thrown, but by doing this check you also prevent duplicate values being entered on the @csv, basically the IN clause does an implicit SELECT DISTINCT on your @csv removing the duplicate values – this is a bad thing if you are entering data and can have duplicates but it’s a good thing if you just want the unique values.
-- Create results table
create table #csvset (
csv_value smallint not null
)
-- Split the string up
exec csv_to_set '55, 99, 1212'
-- Results
select *
from #csvset
drop table #csvset
Filed under: SQL Server