Conditional print for testing scripts.

Question

Post reply

Conditional print for testing scripts.

ben.brugman

SSChampion

Points: 13369
More actions
July 25, 2013 at 7:52 am

#280955

Dear Reader,
For testing I want to replace PRINT statements with a procedure call.
A Global substitute should replace all 'PRINT' with 'EXEC iSPRINT'.
In the procedure I can switch the printing ON (for testing) and OFF.
See below the CALLs which I have tried.
Is there a simple/short solution for my problem where I do not have to edit each print statement ?
Thanks,
ben brugman
--------------------------- Wat I am looking for. (This does produce an error).
iSPRINT 'This is a teststring to print time: ' +convert(varchar(30), getdate(), 126)
iSPRINT 'A seconde teststring to print time: ' +convert(varchar(30), getdate(), 126)
--------------------------- WORKS in isolaton :
iSPRINT 'text 1 This is a teststring to print time: '
--------------------------- WORKS in a script :
exec iSPRINT 'text 1 This is a teststring to print time: '
exec iSPRINT 'text 2 This is a teststring to print time: '
--------------------------- WORKS in a script :
declare @string varchar(300)
set @string = 'text 1 This is a teststring to print time: '+convert(varchar(30), getdate(), 126)
exec iSPRINT @string = @string
set @string = 'text 2 This is a teststring to print time: '+convert(varchar(30), getdate(), 126)
exec iSPRINT @string = @string

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

procedure a supposed to be called with the full EXEC ProcName @Parameters or EXECUTE ProcName @Parameters.

only when a procedure is the only line item (or the first line item in the batch) is the EXEC portion optional.

just add EXEC in front of your iPrint calls:

EXEC iPirnt @Param and you are good to go!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Lowell (7/25/2013)
just add EXEC in front of your iPrint calls:
EXEC iPirnt @Param and you are good to go!

--------------------------- Wat I am looking for. (This does produce an error).

EXEC iSPRINT 'This is a teststring to print time: ' +convert(varchar(30), getdate(), 126)

EXEC iSPRINT 'A seconde teststring to print time: ' +convert(varchar(30), getdate(), 126)

Does not work,

Sorry that my examples where not clear enough.

Ben

Lowell SSC Guru Points: 323504 More actions · Answer 3

i believe that's because , according to SQL rules, you cannot create a parameter from appended objects. you can assign it, or build it before hand, but noty inline

declare @param varchar(200)

SET @param= 'This is a teststring to print time: ' +convert(varchar(30), getdate(), 126)

EXEC iSPRINT @param

SET @param= 'A seconde teststring to print time: ' +convert(varchar(30), getdate(), 126)

EXEC iSPRINT @param

what you could do instead, is have the proc accept , say five varchar params with optional values, then instead of concatenating in line, treat them as parameters.

the problem there, iss you still can convert a date to varchar inline as a parameter. if it's right for the process, you could append the datetime in the procedure itself.

--fails

EXEC iSPRINT 'This is a teststring to print time: ' , CONVERT(varchar(112,getdate())

--works

EXEC iSPRINT 'This is a teststring to print time: ' , 'when Spongebob','IsOn Nickelodeon'

CREATE PROCEDURE iSPRINT @s1 varchar(max),

@s2 varchar(max) = '',

@s3 varchar(max) = '',

@s4 varchar(max) = '',

@s5 varchar(max) = ''

AS

PRINT @s1 + ' ' + @s2 + ' ' + @s3 + ' ' + @s4 + ' ' +@s5 + ' '

GO

you still hit problems with

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ben.brugman SSChampion Points: 13369 More actions · Answer 4

Lowell (7/25/2013)
--fails
EXEC iSPRINT 'This is a teststring to print time: ' , CONVERT(varchar(112,getdate())
--works
EXEC iSPRINT 'This is a teststring to print time: ' , 'when Spongebob','IsOn Nickelodeon'

But this does not fur-fill a purpose anymore.

I have thought of a work around, I will global substitute all PRINT statements with a

-- Print

Because this also affects the functional print statements, the functional print statements will be replaced by a stored procedure (eg. Pxrint). There are far less functional print statements, these I have to edit one at the time, but there are far less of them.

Substituting the '-- PRINT' for 'PRINT' will switch on the teststatements.

The other way around will switch off the test statements.

thanks for your suggestions and your thoughts,

ben brugman

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Using PRINT for troubleshooting can be a real PITA and the output sometimes doesn't show up until the end of the run instead of as the procedure run unfolds.

I recommend that you learn how to use RAISERROR with a "severity" of "0" and the WITH NOWAIT option. Then, add an optional parameter to the proc called @MessageLevel and use IF in the proc to decide whether to execute the RAISERROR based on the value of @MessageLevel.

--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)

ben.brugman SSChampion Points: 13369 More actions · Answer 6

Jeff Moden (7/26/2013)
Using PRINT for troubleshooting can be a real PITA and the output sometimes doesn't show up until the end of the run instead of as the procedure run unfolds.
I recommend that you learn how to use RAISERROR with a "severity" of "0" and the WITH NOWAIT option. Then, add an optional parameter to the proc called @MessageLevel and use IF in the proc to decide whether to execute the RAISERROR based on the value of @MessageLevel.

Thanks for this advise, I'll use this in the future were this is appropriate.

But the problem remains no variables can be added.

RAISERROR ('Now that''s what I call a message!'+convert(varchar(30),getdate(),126), 0, 1) WITH NOWAIT

Does not work.

Offcourse I could first build the string in a variable and pass that. But that would slow down my work and make the code larger and less clear.

Thanks for the advise,

Ben

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

ben.brugman (7/29/2013)
Jeff Moden (7/26/2013)
Using PRINT for troubleshooting can be a real PITA and the output sometimes doesn't show up until the end of the run instead of as the procedure run unfolds.
I recommend that you learn how to use RAISERROR with a "severity" of "0" and the WITH NOWAIT option. Then, add an optional parameter to the proc called @MessageLevel and use IF in the proc to decide whether to execute the RAISERROR based on the value of @MessageLevel.
Thanks for this advise, I'll use this in the future were this is appropriate.
But the problem remains no variables can be added.
RAISERROR ('Now that''s what I call a message!'+convert(varchar(30),getdate(),126), 0, 1) WITH NOWAIT
Does not work.
Offcourse I could first build the string in a variable and pass that. But that would slow down my work and make the code larger and less clear.
Thanks for the advise,
Ben

You've not read about the full capabilities of RAISERROR. I'll be back tonight after work with more of an explanation but RAISERROR will take certain types printf_s notation just like what's available in C#.

--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)

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

No, you will not be able to call another function inside of RAISERROR, but you can use variables in very nice way:

DECLARE @strDT VARCHAR(30) = convert(varchar(30),getdate(),126);

RAISERROR ('Now that''s what I call a message! %s', 0, 1, @strDT) WITH NOWAIT;

RAISERROR has in-built token parsing functionality!

_____________________________________________
"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]

ben.brugman SSChampion Points: 13369 More actions · Answer 9

Eugene Elutin (7/29/2013)
RAISERROR has in-built token parsing functionality!

Thanks all for your responses:

Stop thinking about what you want to do to a print... think, instead, of what you want to do to all the prints.

Problem was over 300 prints. Most for testing purposes.

Response of the prints was not the issue. The issue was to replace the 300 print statements with something which can be switched on and off.

There were all kinds of prints, with parameters (dates, timing, counting, tablenames), multi line etc.

Replacing the 300 prints with different RAISERROR constructions was not a fast and short solution. Maybe the next time.

Solution choosen is global substitute of 'print' with '-- print'.

And replace the functional prints with a stored procedure. (about 15 locations). Not what I was looking for but it works.

Thanks again,

Ben