Not able to print the message in the T-sql code

Question

Not able to print the message in the T-sql code

luckysql.kinda

SSCertifiable

Points: 7816
More actions
November 19, 2009 at 12:06 am

#212781

Hi all,
when I run a big code then I use following print command as well
PRINT 'BEFORE NEW SRNO STARTS @MobileCode= '+@MobileCode+' @MobileCodeNew ='+@MobileCodeNew+'@MobileCodeForN ='+@MobileCodeForN+'@RCOOC ='+@RCOOC+'@MobileCodeForN ='+@MobileCodeForN
This doesn't work eventhough the sqlos reads this line... I verified from the debug as well. Is there any setting that could have made this off?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mike John SSCertifiable Points: 7216 More actions · Answer 1

My bet would be one (or more) of the variables is NULL, and any operation that concatenates NULL with another value will return a null, hence the entire expression is null which in a print statement looks a lot like a blank line!

(Assuming default settings for null behaviour are in use)

Mike John

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

luckysql.kinda (11/19/2009)
This doesn't work eventhough the sqlos reads this line... I verified from the debug as well. Is there any setting that could have made this off?

What is the output (ie what 'are' you getting)? At the least you should see:

BEFORE NEW SRNO STARTS @MobileCode =@MobileCodeNew =@MobileCodeForN =@RCOOC =@MobileCodeForN =

If you get the above (or something like it) your PRINT is working but something in your variables or the way their values are calculated is not.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Richard M. SSCertifiable Points: 7401 More actions · Answer 3

If the output of the PRINT is just a blank line, then one or more of the variables contains a NULL, as Mike stated above.

You probably would have to use a somewhat more "complicated" way to do the print:

PRINT 'BEFORE NEW SRNO STARTS @MobileCode= '+COALESCE(@MobileCode,'')+' @MobileCodeNew ='+COALESCE(@MobileCodeNew,'')+'@MobileCodeForN ='+COALESCE(@MobileCodeForN,'')+'@RCOOC ='+COALESCE(@RCOOC,'')+'@MobileCodeForN ='+COALESCE(@MobileCodeForN,'')

That way you can at least identify which one is the culprit. This will also give you an output as Adam posted above.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 4

Thanks Richard. This seems that some of the variable would have corrupted in mid and got NULL. Now this works.

Thanks again to all.

-Lk

jackbhanded SSC Rookie Points: 27 More actions · Answer 5

To avoid this problem in any complicated stored procedures, initialize the variable to empty before using them in any string manipulation.

e.g.

Declare @MobileCode as varchar(50), @MobileCodeNew as varchar(50), @MobileCodeForN as varchar(50), @RCOOC as varchar(50)

select @MobileCode = ''

select @MobileCodeNew = ''

select @MobileCodeForN = ''

select @RCOOC = ''

-- do what you got to do!

PRINT 'BEFORE NEW SRNO STARTS @MobileCode= '+@MobileCode+' @MobileCodeNew ='+@MobileCodeNew+'@MobileCodeForN ='+@MobileCodeForN+'@RCOOC ='+@RCOOC+'@MobileCodeForN ='+@MobileCodeForN