April 26, 2002 at 4:13 pm
Hi there,
Is any one has the idea how could I pass in or pass out value (concatenated IDs) that is longer than 8000 characters in stored procedure? I am using SQL Server 7 and I tried to create a simple stored procedure for test but it doesn't work. The following is my test procedure:
CREATE PROCEDURE TestLongParameterValue (
@OutValueList TEXT OUT,
@InpValueList TEXT
)
AS
SET NOCOUNT ON
DECLARE
@count INT,
@well_Id VARCHAR(20)
DECLARE CurWellID CURSOR FOR
SELECT uwi
FROM pssl_well
FETCH NEXT FROM CurWellID INTO @well_Id
WHILE @@FETCH_STATUS = 0
BEGIN
IF @InpValueList IS NULL
SET @InpValueList = @well_Id
ELSE
SET @InpValueList = @InpValueList + ', ' + @well_Id
FETCH NEXT FROM CurWellID INTO @well_Id
END
SELECT @InpValueList
RETURN
GO
Any suggestion or solution would be appreciated.
Thanks
Arthur
May 2, 2002 at 7:07 pm
Not sure, but this should do the trick
CREATE PROCEDURE TestLongParameterValue --(
-- wasn't sure you actually where inserting data and needed this parameter @InpValueList TEXT = ''
--)
AS
SET NOCOUNT ON
CREATE TABLE #tmpText (
ColX text
)
INSERT INTO #tmpText (ColX) VALUES ('') --Put @InpValueList here if you are passing something in.
DECLARE @well_Id VARCHAR(20)
DECLARE @PtrVar BINARY(16)
DECLARE CurWellID CURSOR FOR SELECT uwi FROM pssl_well
FETCH NEXT FROM CurWellID INTO @well_Id
--This gets the first record without a , in front drop if you do pass data in thru @InpValueList
IF @@FETCH_STATUS = 0
BEGIN
SELECT @PtrVar = TEXTPTR(ColX)
FROM #tmpText
UPDATETEXT
@PtrVar
NULL
0
@well_Id
FETCH NEXT FROM CurWellID INTO @well_id
END
WHILE @@FETCH_STATUS = 0
BEGIN
/* You may need to create a varchar varible and set = ', ' + @well_Id each loop but not sure */
SELECT @PtrVar = TEXTPTR(ColX)
FROM #tmpText
UPDATETEXT
@PtrVar
NULL
0
', ' + @well_Id
FETCH NEXT FROM CurWellID INTO @well_Id
END
SELECT ColX FROM #tmpText
DROP TABLE #tmpText
GO
Let me know what happens.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply