[thelist] Stored procedure

[Ken Schaefer]

It can be done inside a sproc, but it's not neat. T-SQL isn't really suited
to this type of operation. Here is an example using a single comma separated
string

CREATE PROC usp_mySproc

    @myString    varChar(100)

AS

    DECLARE @left AS int
    DECLARE @remainingString AS varChar(100)

    SET @remainingString = @myString

    SET @left = CHARINDEX(',', @remainingString)

    BEGIN TRAN myTran

    WHILE @left > 0

        BEGIN
        INSERT INTO
            myTable
        VALUES
        (
            SUBSTRING(@remainingString, 1, @left - 1)
        )

        IF @@ERROR != 0
        BEGIN
            ROLLBACK TRAN myTran
            RETURN -1
        END

        SET @remainingString = RTRIM(SUBSTRING(@remainingString, @left + 1,
LEN(@remainingString)))

        SET @left = CHARINDEX(',', at remainingString)

    END

    IF LEN(@remainingString) > 0
    INSERT INTO myTable VALUES (@remainingString)

GO


Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Stevenson Ngila" <Stevenson at epr.footman-walker.com>
Subject: RE: [thelist] Stored procedure


: Am using coldfusion. But i always prefer leaving the db processing to DB
: instead of using coldusion. But seems like there is no way out!
:
: -----Original Message-----
: From: thelist-admin at lists.evolt.org
: [mailto:thelist-admin at lists.evolt.org]On Behalf Of Ken Schaefer
: Sent: 28 January 2003 09:06
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] Stored procedure
:
:
: Do you have to do this in a sproc? What's your frontend?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Stevenson Ngila" <Stevenson at epr.footman-walker.com>
: Subject: [thelist] Stored procedure
:
:
: : I have two strings i.e. string1 = "peter,john,steve" and string2=
: : "mary,jane,janet".
: :
: : (1)i want to loop through string1 and string2 and get "peter" from
string1
: : and "mary" from string2 and insert both name in to a table.
: : (2) I want to repeat the above procedure and get "john" and "jane" and
: : insert them in to a table.
: :
: : Can someone please assist me with the stored procedure to do these in
SQL
: : server 2000!
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~