Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditional print for testing scripts. Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 251, Visits: 1,197
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

Post #1477497
Posted Thursday, July 25, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477500
Posted Thursday, July 25, 2013 9:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 251, Visits: 1,197
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
Post #1477572
Posted Thursday, July 25, 2013 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477591
Posted Friday, July 26, 2013 10:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 251, Visits: 1,197
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
Post #1478107
Posted Friday, July 26, 2013 8:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478262
Posted Monday, July 29, 2013 2:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 251, Visits: 1,197
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
Post #1478461
Posted Monday, July 29, 2013 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478583
Posted Monday, July 29, 2013 8:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,873, Visits: 5,190
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1478615
Posted Tuesday, July 30, 2013 4:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 251, Visits: 1,197
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
Post #1478919
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse