SQL Server Performance

Parse value Function to SP in SQL 7

Discussion in 'General Developer Questions' started by SQL DBA, Jul 11, 2008.

  1. SQL DBA New Member

    Hi Experts,
    This is a funtion to Parse values. This is working fine in sql 2000. Since FUNCTION is not supported in SQL 7, it is not working.
    Can anyone modify(into Store Procedure) in such a way that it can execute in SQL 7
    CREATE FUNCTION ParseValues
    (@String varchar(8000),
    @Delimiter char(1)
    )
    RETURNS @RESULTS TABLE
    (ID int identity(1,1),
    Val varchar(100)
    )
    AS
    BEGIN
    DECLARE @Value varchar(100)

    WHILE @String is not null
    BEGIN
    SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
    @String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
    INSERT INTO @RESULTS (Val)
    SELECT @Value
    END
    RETURN
    END
    Thanks in advance.
  2. Madhivanan Moderator


    CREATE Procedure ParseValues
    (@String varchar(8000),
    @Delimiter char(1)
    )
    AS
    create table #RESULTS
    (ID int identity(1,1),
    Val varchar(100)
    )
    DECLARE @Value varchar(100)

    WHILE @String is not null
    BEGIN
    SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
    @String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
    INSERT INTO #RESULTS (Val)
    SELECT @Value
    SELECT * FROM #RESULTS
  3. SQL DBA New Member

    Thanks for your reply.
    While executing the script im getting error
    Server: Msg 170, Level 15, State 1, Procedure ParseValues, Line 20
    Line 20: Incorrect syntax near '#RESULTS'.
    Please help me in fixing this.
  4. Madhivanan Moderator

    Put END before SELECT * FROM #RESULTS
  5. SQL DBA New Member

    Thank you .
    It is working fine

Share This Page