ramlicious Blogs by Tina & Prabhu

August 10, 2010

Character-separated List to Row Values

Filed under: SQL Server — Prabhuram @ 2:08 am

We will create CSV to Rows, just as we did Rows to CSV.

CREATE FUNCTION dbo.fn_ConvertCSVToTable ( @Param VARCHAR(8000) )
RETURNS @result TABLE ( Item VARCHAR(100) )
AS
BEGIN
– Declare @Param VARCHAR(8000)
– Declare @result TABLE ( Item VARCHAR(10) )
– Set @Param = '1,2,33,4,5'
Declare @Position integer
Declare @Item varchar(8000)
Set @Position = CharIndex( ',', @Param )
While @Param <> ''
Begin
  If @Position > 0
  Begin
    Set @Item = Substring( @Param, 1, @Position - 1 )
    Set @Param = Substring( @Param, @Position + 1, 8000 )
  End
  Else
  Begin
    Set @Item = @Param
    Set @Param = ''
  End
Insert @result Values ( @Item )
Set @Position = CharIndex( ',', @Param )
End
– Select * From @Result
Return
END
go

Note that this returns a table and is not a scalar-valued function. So this has to be called as:

select * from dbo.fn_ConvertCSVToTable ('1,2,3,4,5,6,7,8,9,0') Numerals

and not:

select dbo.fn_ConvertCSVToTable ('1,2,3,4,5,6,7,8,9,0') Numerals 

and here is an implementation:

declare @db VARCHAR(20)
declare @tables VARCHAR(max)
declare @qry VARCHAR(max)
SET @db='Northwind'
SET @tables='Categories, Customers, Employees, [Order Details]'
SET @qry = 'set nocount on;'+ char(13) + char(10)
select @qry = @qry + 'Print ''Count of rows in ' + Item + ''';' + 'select count(*) Number from ' + @db + '.dbo.' + Item + ';' + char(13) + char(10) from Northwind.dbo.fn_ConvertCSVToTable(@tables)
SET @qry = @qry + 'set nocount off;'+ char(13) + char(10)
print @qry
exec (@qry)

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress