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:

Comments

# Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation &laquo; using &#8230;

Pingback from  Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation  &laquo; using &#8230;