[thelist] Stored procedure
Ken Schaefer
ken at adOpenStatic.com
Tue Jan 28 00:50:01 CST 2003
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!
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
More information about the thelist
mailing list