CURSOR and a Variable issue - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

CURSOR and a Variable issue

Author

Topic

crownclit
Starting Member

20 Posts

Posted - 2014-12-13 : 07:29:31

i am trying to generate a MetaTag keywords out of the table but having a problem with passing a cursor variable into another variable.
Tried SET, SELECT and I am not getting any values. Can someone please help me out? issue is in the SET statement.

DECLARE @Keyword NVARCHAR(MAX)
DECLARE @Key NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR

SELECT [BornAs] + ',' AS Lastname
FROM [dbo].[tblFamilyMember] AS t2
WHERE [BornAs] IS NOT NULL AND [BornAs] <> '?'
GROUP BY [BornAs]

UNION

SELECT [LastName] + ',' AS Lastname
FROM [dbo].[tblFamilyMember] AS t2
WHERE [LastName] IS NOT NULL AND [LastName] <> '?'
GROUP BY [LastName]

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Key

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Keyword = @Keyword + @Key
FETCH NEXT FROM db_cursor INTO @Key
END

CLOSE db_cursor
DEALLOCATE db_cursor

UPDATE [dbo].[tblWebConrolStaticItem] SET [Value] = '"' + @Keyword + '"'
WHERE [WebPageName] = '~/pagename.aspx' AND [ControlID] = 'Tag' AND [Text] = 'Keywords' AND [Val] = '3'

Thank you

CC

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-13 : 08:41:00

since you didn't initialize variable called keyword, it defaults to null.
then in the body of your loop you add the variable retrieved from the cursor. But, null plus anything equals null.

for what it's worth, you do not need a cursor to do what you're trying to do.

crownclit
Starting Member

20 Posts

Posted - 2014-12-13 : 17:41:41

thank you gbritton

I have changed first line to DECLARE @Keyword NVARCHAR(MAX) = '' and all works fine now.
I thought that SET command will initialize my variable, obviously I was wrong.
I used cursor cause I did not know any better ways to achieve my goal. Previously I tried STUFF(.......FOR XML PATH ('')), 1, 1, '')
but got this error and didn't know how to fix it :-).

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

Thank you again.

CC

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-14 : 08:27:30

select ...
from (<your original selects here) sub
for xml...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources