How to change NULL into a value when executing a FETCH

Question

How to change NULL into a value when executing a FETCH

TheKasper

SSC-Addicted

Points: 433
More actions
May 15, 2012 at 3:14 am

#392261

I just wonder how I can solve a problem with a NULL-value being change into another value when I fetch the data from my statement into the variable (var_5)...
If the bold-text column (tb_2.column_2) is NULL then I would like to put the value "Undefined" into the bold-text variable (var_5).
BEGIN
DECLARE C2 CURSOR READ_ONLY
FOR
SELECT
count(*),
sum(tb_1.column_1),
sum(tb_1.column_2),
SUM(tb_1.column_3),
tb_2.column_2
FROM tb_1
LEFT OUTER JOIN tb_2 ON tb_1.column_4 = tb_2.column_4
WHERE tb_1.column_5 = criteria
GROUP BY tb_2.column_2
END
OPEN C2
FETCH NEXT FROM C2 INTO
@var_1, @var_2, @var_3, @var_4, @var_5

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

ISNULL(tb_2.column_2, 'Undefined')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Eugene Elutin (5/15/2012)
ISNULL(tb_2.column_2, 'Undefined')

You didn't mention the 'c' word...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TheKasper SSC-Addicted Points: 433 More actions · Answer 3

Sometimes the answer is right in front of you... Tnx!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Phil Parkin (5/15/2012)
Eugene Elutin (5/15/2012)
ISNULL(tb_2.column_2, 'Undefined')
You didn't mention the 'c' word...

Or, yeah!:cool:

To OP:

Are you sure you need a cursor for what you're doing?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

TheKasper SSC-Addicted Points: 433 More actions · Answer 5

Yes I do...

I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

jonas.kasper (5/15/2012)
Yes I do...
I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...

Looks like legitimate reason to me for using cursor.

Any objections, anyone?

😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Eugene Elutin (5/15/2012)
jonas.kasper (5/15/2012)
Yes I do...
I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...
Looks like legitimate reason to me for using cursor.
Any objections, anyone?
😉

Actually, I do this all the time and I never needed a cursor to do it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 8

RBarryYoung (5/15/2012)
Eugene Elutin (5/15/2012)
jonas.kasper (5/15/2012)
Yes I do...
I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...
Looks like legitimate reason to me for using cursor.
Any objections, anyone?
😉
Actually, I do this all the time and I never needed a cursor to do it.

"There must be 50 ways to lose your cursors..." 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Jeff Moden (5/15/2012)
RBarryYoung (5/15/2012)
Eugene Elutin (5/15/2012)
jonas.kasper (5/15/2012)
Yes I do...
I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...
Looks like legitimate reason to me for using cursor.
Any objections, anyone?
😉
Actually, I do this all the time and I never needed a cursor to do it.
"There must be 50 ways to lose your cursors..." 😀

Yes, at least that many.

😎

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

TheKasper SSC-Addicted Points: 433 More actions · Answer 10

OK. I could use a bunch of while-loops but hey, I think it's an easy and comfortable way to write my code. And it's neat for the eye...

By the way here's another problem I need help with...

http://www.sqlservercentral.com/Forums/Topic1300914-391-1.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

jonas.kasper (5/16/2012)
OK. I could use a bunch of while-loops but hey, I think it's an easy and comfortable way to write my code. And it's neat for the eye...
By the way here's another problem I need help with...
http://www.sqlservercentral.com/Forums/Topic1300914-391-1.aspx

When we say "No Cursors", we also mean "And Definitely No While Loops". I never use either one. Not even for my mail jobs.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 12

RBarryYoung (5/15/2012)
Jeff Moden (5/15/2012)
RBarryYoung (5/15/2012)
Eugene Elutin (5/15/2012)
jonas.kasper (5/15/2012)
Yes I do...
I am creating a job that will send emails every morning showing different data that has occured during the last day. Some of the data that is sent sometimes holds the value NULL so to spare you 100 rows of code I simply cut out a peice to show here...
Looks like legitimate reason to me for using cursor.
Any objections, anyone?
😉
Actually, I do this all the time and I never needed a cursor to do it.
"There must be 50 ways to lose your cursors..." 😀
Yes, at least that many.
😎

Just slip out the back-end Jack

Makin no query plans Sam ...

Converting oxygen into carbon dioxide, since 1955.