Creating a function that returns a temp table

[BobBob10]

Hi I ant to create a function that returns a temp table. What I want to do is pass a string into a funtion then this function returns a table.

My string will be '215,216,303,782'
The table it returns will be

ID
215
216
303
782

My function will have the main code like



CREATE TABLE #ClientRef (Client int)

DECLARE @OldPos AS int
DECLARE @NewPos AS int
DECLARE @Counter As Int

SET @Counter = 1
SET @Client_Ref = ',' + @Client_Ref + ','

WHILE @Counter + 1 < len(@Client_Ref)
BEGIN
SET @OldPos = CHARINDEX( ',', @Client_Ref, @Counter)
SET @NewPos = CHARINDEX( ',', @Client_Ref, @OldPos + 1)

INSERT INTO #ClientRef (Client)
SELECT SUBSTRING ( @Client_Ref , @OldPos + 1, @NewPos - (@OldPos + 1))

SET @Counter = @NewPos - 1

END

However, this will not work.

Any ideas???

 

[sodakotahusker]

Use a table variable instead of a temp table.

I never used them until recently cause I just had not taken the time to learn how. They are very easy to use and now I use them all the time. They are supposed to be better for performance.

 

[gmmastros]

Here's your function...

Create Function dbo.Split
    (
    @CommaDelimitedFieldNames Varchar(8000), 
    @Character VarChar(20)
    ) 
Returns @Tbl_FieldNames 
Table     (
        Id Integer Identity(1,1),
        FieldName VarChar(100)
        ) 
As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @Character 

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@Character,@CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Use it like this...

Select * from dbo.Split('215,216,303,782', ',')


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom