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

2 Replies

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.

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!

This site uses Akismet to reduce spam. Learn how your comment data is processed.