[RESOLVED] SPROC - Deliminated String

[Mark Gambo]

With help from techgnome and szlamany I am attempting to write a SPROC that will accept a delimited string (ie: Office 1,Office 2,Office 3) as a parameter. So after reviewing this POST by szlamany, I attempted to create a SPROC:

GO
CREATE PROCEDURE dbo.TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
    @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
    @CP int /*Len of String */
    @SV varchar(50) /*Holds Result */
 
AS
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End

But when I run it I get the following errors:

Msg 102, Level 15, State 1, Procedure TestSP, Line 5
Incorrect syntax near '@ListTbl'.
Msg 137, Level 15, State 2, Procedure TestSP, Line 9
Must declare the scalar variable "@ListStr".
Msg 137, Level 15, State 2, Procedure TestSP, Line 11
Must declare the scalar variable "@ListStr".
Msg 137, Level 15, State 2, Procedure TestSP, Line 12
Must declare the scalar variable "@CP".
Msg 137, Level 15, State 2, Procedure TestSP, Line 14
Must declare the scalar variable "@ListStr".
Msg 137, Level 15, State 2, Procedure TestSP, Line 15
Must declare the scalar variable "@ListStr".
Msg 137, Level 15, State 2, Procedure TestSP, Line 19
Must declare the scalar variable "@ListStr".
Msg 137, Level 15, State 1, Procedure TestSP, Line 20
Must declare the scalar variable "@ListStr".
Msg 1087, Level 15, State 2, Procedure TestSP, Line 22
Must declare the table variable "@ListTbl".

I don't understand these error, does anyone have any ideas?


P.S. I want this SPROC to run against either a MSSQL 7.0 or MSSQL 2005 Database.
Thanks!

[si_the_geek]

It's been a while for me... but shouldn't there be brackets around the parameter list, and/or commas between each parameter?

[Mark Gambo]

Stupid mistake the "AS" was in the wrong place, here is my corrected code:

IF EXISTS (select * from syscomments where id = object_id ('dbo.TestSP'))
    DROP PROCEDURE dbo.TestSP
 
GO
CREATE PROCEDURE dbo.TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
AS
 
 
DECLARE @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End