SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional print for testing scripts.


Conditional print for testing scripts.

Author
Message
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 2417
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


Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71318 Visits: 40930
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
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 2417
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
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71318 Visits: 40930
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
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 2417
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
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 2417
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
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214026 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12344 Visits: 5478
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
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2536 Visits: 2417
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search