Tuesday, November 22, 2005

SQL: Handy function

I have a lot of stored procedures that I need to retreive, update, delete multiple records based on ID (usually on a screen where a user can select the customers to run a report on). I have seen a lot of developers create dynamic sql (which I hate) or call the same stored proc multiple times (which I hate). I created a little sql function that takes a string of delmited data and turns it into a table:


CREATE fuNCTION fnStringToCol (@data varchar(8000), @delimiter varchar(100))
RETURNS @output TABLE (data varchar(8000))
AS
BEGIN
declare @position int
declare @start int
declare @end int
declare @part varchar(8000)

if (charindex(@delimiter, @data) = 0) --if only one value append a , so that it still gets parsed
set @data = @data + ','

set @position = charindex(@delimiter, @data)
insert into @output values (ltrim(rtrim(substring(@data, 0, @position))))

while ((charindex(@delimiter, @data, @position)) <> 0)
begin
set @start = charindex(@delimiter, @data, @position)
set @end = charindex(@delimiter, @data, @start+1)
if (@end = 0)
set @end = len(@data) - @start

set @part = ltrim(rtrim(substring(@data, @start+1, abs(@end - @start-1))))
insert
into @output
values (@part)
set @position = charindex(@delimiter, @data, @position) + 1
end
return
END


To use this is quite easy:

declare @producers
set @producers = '1,7,35,26'

select *
from producer
where producer_id in (select * from dbo.fnStringToCol(@producers, ','))

0 Comments:

Post a Comment

<< Home