Multiplication Table Using WHILE Loop

Spending sometime for T-SQL on weekends made me write a stored procedure that returns Multiplication Table for any given number.

Stored Procedure Code:

CREATE PROCEDURE dbo.usp_MultiplicationTable

@N INT

AS

BEGIN

SET NOCOUNT ON;

            DECLARE @I INT = @N, @J INT = 1

            DECLARE @TV TABLE (RESULT VARCHAR(20))

            WHILE (@J <= 10)

            BEGIN

                        INSERT INTO @TV

                        SELECT CAST(@I AS VARCHAR) + CHAR(32) + '*' + CHAR(32) +

                        CAST(@J AS VARCHAR) + CHAR(32) + '=' + CHAR(32) + CAST(@I * @J AS VARCHAR)

                       

                        SET @J = @J + 1

            END

            SELECT * FROM @TV;

END

Execution Code:

On passing '2' as the input value to the stored procedure, it returned below output:

Hope this helps someone!

Conclusion: T-SQL is like a girl friend. You get close to it, it loves back. You go away, it starts hating you. So Happy TSQLing ;)

Pyramid Structure using TSQL Code

Do you remember the college days, where one used write to simple C++ programs? Great days right? Today I thought of writing a such fun code to print a pattern. And this time using T-SQL.

T-SQL, not just programming business logic codes but also used to make fun. :)

Here is a small example of how we can use TSQL Code to draw a Pyramid Structure using Asterisk Symbol.

SET NOCOUNT ON;

DECLARE @T TABLE (ID VARCHAR(25))

DECLARE @SYM VARCHAR(1)
SET @SYM = '*'
DECLARE @I INT
SET @I = 1
DECLARE @J INT
SET @J = 6

INSERT INTO @T SELECT SPACE(@J) + @SYM
WHILE (@I <= @J)
BEGIN
DECLARE @SPACE INT
SET @SPACE = @J - @I
INSERT INTO @T
SELECT 
SPACE(@SPACE) + 
CASE 
WHEN @I = 1 THEN @SYM + @SYM
WHEN @I = 2 THEN @SYM + @SYM + @SYM
WHEN @I = 3 THEN @SYM + @SYM + @SYM + @SYM
WHEN @I = 4 THEN @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 5 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 6 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
ELSE @SYM 
END + 
CASE 
WHEN @I = 1 THEN @SYM
WHEN @I = 2 THEN @SYM + @SYM
WHEN @I = 3 THEN @SYM + @SYM + @SYM
WHEN @I = 4 THEN @SYM + @SYM + @SYM + @SYM
WHEN @I = 5 THEN @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 6 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
ELSE @SYM 
END + SPACE(@SPACE)
SET @I = @I + 1
END

SELECT * FROM @T;



On executing the above code you will find the Pyramid Structure. Happy TSQLing. ;)