Cursor Error: Subquery returned more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor Error: Subquery returned more than 1 value

Author

Topic

hs123456
Starting Member

6 Posts

Posted - 2010-02-05 : 10:52:39

Here is the sproc:

DECLARE @submission NVARCHAR(MAX)
DECLARE @temp NVARCHAR(MAX)
DECLARE @fName NVARCHAR(MAX)
DECLARE @fValue NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #SplitValues (
fName NVARCHAR(MAX),
fvalue NVARCHAR(MAX)

)

DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
Select Replace(cast(MyTable.Submission as nvarchar(max)),'***', ';') from MyTable

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @submission

WHILE @@FETCH_STATUS = 0
BEGIN

WHILE len(@submission) > 0
BEGIN

SET @fName = (select substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1) from MyTable)

SET @fValue = (select substring(substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))), 0, charindex(';',substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))))) from MyTable)

INSERT INTO #SplitValues VALUES (@fName,@fValue)
SET @submission = (substring(@submission, 0, len(@submission) - len(@fValue)))

END

FETCH NEXT FROM db_cursor INTO @submission
END

CLOSE db_cursor
DEALLOCATE db_cursor

-----------------------------------------------------------
Here is the problem:

Select Replace(cast(MyTable.Submission as nvarchar(max)),'***', ';') from MyTable

This query will give me all the records:

1. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
2. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
3. ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3

when I use cursor, it is bringing back all records and not just 1st record, and throws Error:"Subquery returned more than 1 value" at SET @fName

SET @fName = (select substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1) from MyTable)
This query should give me:
columnname1

SET @fValue = (select substring(substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))), 0, charindex(';',substring(@submission, len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) +2)), len(@submission)-len(substring(@submission, 2, charindex(';', substring(@submission, 2, len(@submission)-1 )) -1))))) from MyTable)
This query should give me:
columnvalue1

Please Help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:54:31

duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139419

please dont cross post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources