Hello SQL devellopers,

this is my first post using SQL-Scripts and I have to find out, which are the right tools, to present sql code here on blogger.com.

I present a small helper function which I will use to write a wrapper around the SQL Select-statement. Some academic egg-heads want to make me believe that columns have to be addressed by name, I'm going to teach them how to it by column numbers ( in my next post).

Second this is an example of my coding style. I do not use VARCHARN, [] (the damned reincarnation  of quoted identifiers) and I use the dbo. prefix at sensible places (contrary to the codegenerator in SQL-Server Management Studio ;.-).

This style has matured during one decade of SQL coding. My use of () brackets around the function parameters is nonstandard, but it works.

My style is develloped with the goal to help me convert T-SQL to PLSQL.



if object_id ('splitColumnNumberList') > 0
    drop function splitColumnNumberList
go

create function dbo.splitColumnNumberList
(
    @list varchar(8000),
    @del varchar(255) = ','
) returns @numbers table (lfd int identity(1,1),value int)
as
begin
    declare @pos    integer
    -- select CHARINDEX(',', '1,2,3')
    set @pos = CHARINDEX(@del, @list)
    while @pos > 0
        begin
            insert into  @numbers values(substring(@list, 1, @pos - 1))
            set @list = substring(@list, @pos + len(@del), 8000)
            set @pos = CHARINDEX(@del, @list)
        end

    insert into  @numbers values (@list)

    return
end

go

Bernd