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

• PRINT statement in T-SQL [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.

Recently, a colleague brought an interesting query to me – the design for one of their modules required that records in a table be grouped into chunks of a specified integer value and each record within this group be assigned a number starting from 1. Hence, if I had 20 records in a table and the group size was 5 records, I would end up with 4 groups and the records would need to be numbered from 1-5 within each group.

Note here that I do not want a fixed number of groups (I can easily achieve this using NTILE). The groups can reach any number – what I need is a fixed number of elements within a group.

This post documents the first solution that came to my mind. While that particular deliverable is complete by the time I write this post, I look forward to hearing about other solutions/suggestions to achieve this requirement.

Creating the Test Scenario

To create a test scenario, I first create a simple test table and populate it with some test data. To make things interesting, I have inserted a prime number of records.

USE tempdb;
GO
SET NOCOUNT ON
--Safety Check
IF OBJECT_ID('#recordDistributionTest','U') IS NOT NULL
   DROP TABLE #recordDistributionTest;
GO
--Create the test table
CREATE TABLE #recordDistributionTest
       (rowNum INT IDENTITY(1,1),
        numVal AS (rowNum * 10)
       );
GO
--Insert 17 records into the test table
INSERT INTO #recordDistributionTest
DEFAULT VALUES;
GO 17

The solution

The solution that first came to my mind was to use the modulo operator (%) on the identity/row number value.

For the purposes of this example, I have used a @recordsInAGrop variable which is set to 5.

--Variable to hold the number of records in a group
DECLARE @recordsInAGroup INT = 5;
--Perform the grouping when fetching the records
SELECT rdt.rowNum AS RowNumber,
       rdt.numVal AS RecordValue,
       CASE WHEN (rdt.rowNum % @recordsInAGroup) = 0
            THEN @recordsInAGroup
            ELSE (rdt.rowNum % @recordsInAGroup)
       END AS RowNumberWithinGroup
FROM #recordDistributionTest AS rdt;
GO

Following is the screenshot of the result obtained from this method.

As can be seen from the result, the first 15 records were grouped into 3 groups of 5 each. The last 2 records don’t belong to any group of the size specified in @recordsInAGroup variable and are therefore easily identifiable for further processing.

Do you have any suggestions on how to achieve this requirement in a much more efficient manner? Do drop in a line with your thoughts/suggestions.

Until we meet next time,

Be courteous. Drive responsibly.