cursor - 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 2008 Forums

Transact-SQL (2008)

cursor

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 10:08:49

Hi,
The print sattement below does not show any values but if I take out the + in
set @values += ...
i.e. to be come set @values = ...
then the print works.

Do you see what I am doing wrong please?
Thanks

declare @item varchar(50)
declare @values varchar(1000)

declare valuesCursor cursor for
select item from fnSplit(@Value, ',')
open valuesCursor
fetch next from valuesCursor into @item
while @@FETCH_STATUS = 0
begin
set @values += char(39) + convert(varchar(50), @item) + char(39) + ','
print @values
fetch next from valuesCursor into @item
end
close valuesCursor
deallocate valuesCursor

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 10:17:09

Hello arkiboys,

Perhaps @values needs to be initialized? Something like;

declare @values varchar(1000) = ''

HTH.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-18 : 10:30:31

Yes, it is not initialized hence it is NULL value and concatenating with NULL gives NULL as result
when SET CONCAT_NULL_YIELDS_NULL is ON

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-18 : 10:36:36

Solved.
Many thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 10:38:10

Why are you using a cursor?

set @values = COALESCE(@values,',','') + char(39) + item + char(39) + ','
FROM fnSplit(@Value, ',')

And I've never seen += before

And, it looks like you are taking a delimited string and trurning it into a delimited string.

wtf

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-18 : 10:41:26

If all you're doing is putting double quotes around comma-separated elements:

SET @Values='"' + REPLACE(@Value, ',', '","') + '"'

Absolutely no need to split this into rows, much less using a cursor.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 10:48:46

I suggested that in another topic and was told. It does NOT work.

Without any suggestion of why it didn't work.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 14:51:09

Brett:

 Set @foo = @foo +1
       Set @foo += 1

That is a short cut they introduced in 2005 (i think. maybe 2008)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 16:41:32

You mean another undocumented "Feature"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 16:43:41

huh

DECLARE @foo int; SET @foo = 0

Set @foo = @foo +1

SELECT @foo

Set @foo += 1

SELECT @foo

Why would you bother to obfuscate this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 17:22:11

you can now do stuff like this also in 2008


DECLARE @foo INT = 0

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-18 : 17:23:40

quote:
Why would you bother to obfuscate this?

I'm not 100% sure but I believe compound assignment is ANSI/ISO SQL, it's certainly standard (and old) in many other programming languages. It's fully documented and available in SQL Server 2008.

What makes you think it's obfuscated or undocumented?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-18 : 17:42:22

quote:
Originally posted by X002548

You mean another undocumented "Feature"?

Brett

<needlessly long sig removed>

What are you talking about? It's fully documented. It's called a Compound Assingment Operator. Although,new to SQL Server in 2008, they've been in programing languages for decades.

http://msdn.microsoft.com/en-us/library/ms189484.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources