#0346 – SQL Server – Using CASE with a PRINT statement


Informational and alert messages during execution of a T-SQL script are the most preferred method to update the user on the progress of the task being executed. These messages are generated by the various PRINT statements embedded in the script.

Often there may be a requirement to display a particular message when a given set of conditions is met and another message otherwise. In such cases, I used to write a  standard IF…ELSE block and embed the PRINT statements within.

So, for example if the task at hand was to determine whether two numbers are equal or not, the traditional code would have been:

USE tempdb;
GO

--Traditional method
IF OBJECT_ID('dbo.proc_TraditionalNumberCompare','P') IS NOT NULL
DROP PROCEDURE dbo.proc_TraditionalNumberCompare;
GO

CREATE PROCEDURE dbo.proc_TraditionalNumberCompare
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON;

IF (@intA - @intB ) = 0
PRINT 'Traditional Method - Both integers are the same.';
ELSE IF (@intA - @intB) > 0
PRINT 'Traditional Method - Integer A is greater than Integer B';
ELSE IF (@intA - @intB) < 0
PRINT 'Traditional Method - Integer B is greater than Integer A';
ELSE
PRINT 'Unreachable code!';
END
GO

--Tests
EXEC dbo.proc_TraditionalNumberCompare @intA = 5, @intB = 5;
GO
/* RESULT
Traditional Method - Both integers are the same.
*/

EXEC dbo.proc_TraditionalNumberCompare @intA = 2, @intB = 5;
GO
/* RESULT
Traditional Method - Integer B is greater than Integer A
*/

EXEC dbo.proc_TraditionalNumberCompare @intA = 5, @intB = 3;
GO
/* RESULT
Traditional Method - Integer A is greater than Integer B
*/

However, I recently realized that if the expression returns a string value, it can directly be used in a single PRINT statement, i.e. I can rewrite the logic to compare two integers shown above into a single statement as demonstrated below:

USE tempdb;
GO

IF OBJECT_ID('dbo.proc_NumberCompare','P') IS NOT NULL
DROP PROCEDURE dbo.proc_NumberCompare;
GO

CREATE PROCEDURE dbo.proc_NumberCompare
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON;

PRINT CASE
WHEN (@intA - @intB) = 0
THEN 'Both integers are the same.'
WHEN (@intA - @intB) > 0
THEN 'Integer A is greater than Integer B'
WHEN (@intA - @intB) < 0
THEN 'Integer B is greater than Integer A'
ELSE 'Unreachable code!'
END;
END
GO

--Tests
EXEC dbo.proc_NumberCompare @intA = 5, @intB = 5;
GO
/*RESULT
Both integers are the same.
*/

EXEC dbo.proc_NumberCompare @intA = 2, @intB = 5;
GO
/*RESULT
Integer B is greater than Integer A
*/

EXEC dbo.proc_NumberCompare @intA = 5, @intB = 3;
GO
/*RESULT
Integer A is greater than Integer B
*/

I’m sure you will also find this flavour of implementing the PRINT statement useful. Do let me know your thoughts before you go.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0346 – SQL Server – Using CASE with a PRINT statement

  1. Henrik Staun Poulsen

    If you create it as a Table-Value function, you’re likely to get better performance…
    Like this:
    USE tempdb
    GO

    CREATE FUNCTION NumberCompare(
    @intA INT,
    @intB INT
    )
    RETURNS TABLE
    AS RETURN
    SELECT CASE
    WHEN (@intA – @intB) = 0
    THEN ‘Both integers are the same.’
    WHEN (@intA – @intB) > 0
    THEN ‘Integer A is greater than Integer B’
    WHEN (@intA – @intB) < 0
    THEN 'Integer B is greater than Integer A'
    ELSE 'Unreachable code!'
    END AS NumberCompare;
    go

    SELECT 5 AS IntA,* from (SELECT 1 AS IntB UNION ALL SELECT 5 UNION ALL SELECT 8) AS input
    OUTER APPLY NumberCompare( 5, input.IntB);

    DROP FUNCTION NumberCompare

    Liked by 1 person

    Reply
  2. henrikstaunpoulsen

    If you use a table-value function, you may get better performance.
    like this:
    USE tempdb
    GO

    CREATE FUNCTION NumberCompare(
    @intA INT,
    @intB INT
    )
    RETURNS TABLE
    AS RETURN
    SELECT CASE
    WHEN (@intA – @intB) = 0
    THEN ‘Both integers are the same.’
    WHEN (@intA – @intB) > 0
    THEN ‘Integer A is greater than Integer B’
    WHEN (@intA – @intB) < 0
    THEN 'Integer B is greater than Integer A'
    ELSE 'Unreachable code!'
    END AS NumberCompare;
    go

    SELECT 5 AS IntA,* from (SELECT 1 AS IntB UNION ALL SELECT 5 UNION ALL SELECT 8) AS input
    OUTER APPLY NumberCompare( 5, input.IntB);

    go
    DROP FUNCTION NumberCompare

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s