February 8, 2010 at 11:34 am
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!
February 8, 2010 at 12:07 pm
The problem is that your statement
SET @fName = (select [something] from MyTable)
will return one value for every row in MyTable, which cannot be assigned to a variable of the data type you're using. Same applies to @fValue (which would also fail if you'd eliminate the @fName part).
What exactly are you trying to do? Please post some more details (e.g. sample values from MyTable and expected results). It seems like you don't need a cursor at all.
February 8, 2010 at 1:00 pm
I am modifying one column from MyTable
This column has values like this:
Record 1: ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
Record 2: ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
Record 3: ;columnname1; columnvalue1 ;columnname2; columnvalue2 ;columnname3; columnvalue3
expected results are:
Record 1:
@fName = columnname1
@fValue = columnvalue1
Insert into Temp Table(@fName, @fValue)
@fName = columnname2
@fValue = columnvalue2
Insert into Temp Table(@fName, @fValue)
@fName = columnname3
@fValue = columnvalue3
Insert into Temp Table(@fName, @fValue)
so on and so forth with Record 2 and Record 3
Why does cursor get all 3 records at the same time and gives error????
Please Help!
February 8, 2010 at 1:26 pm
hs103 (2/8/2010)
...Why does cursor get all 3 records at the same time and gives error????
Please Help!
Please see my previous post:
You're doing a SELECT FROM MyTable. This will result in all records you have in that table.
How would SQL Server know that you're talking about the "first" row when you run your select statement? It will always return ALL rows if you don't specify a WHERE clause.
To solve your issue I'd use a split string function to get your values separated.
(please search this site for samples or have a look at the TallyTable article I referenced in my signature for some sample code).
In general, it's not really a good database design to have column names and values stored the way you describe. How would you handle a column value of 'this is a sample; you can ignore it'? (Notice the semicolon in the middle of the text. This will totally mess up your insert proc!)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply