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 ;)

Best Practices for Stored Procedure

Stored Procedure: It is considered as one of the most optimized database object in SQL Server database. If we implement few best practices, it can give more efficient results.

Best Practices: 

1. Use "SET NOCOUNT ON" as the first line of the stored procedure body.
2. Do not use SP_ or sys_ prefixes for naming user stored procedures.
3. Always include schema names prefix for stored procedure and other database objects used in stored procedure. It reduces processing time.
4. Avoid 'SELECT *' and specify the columns in SELECT statement.
5. Use explicit transactions by using BEGIN/END TRANSACTION.
6. Use TRY/CATCH for Error Handling.
7. Defined NULL or NOT NULL for Temporary Table Columns.
8. Last but not least implement best practices for the queries used in Stored Procedure.

Multiple Result Sets in single Stored Procedure

SQL Server 2012 introduces a new feature called "WITH RESULT SETS".
It makes the stored procedure returning multiple result sets. Lets see an illustration on how this new thing works.

Stored Procedure Code:
CREATE PROCEDURE dbo.usp_MultipleResultSets
AS
BEGIN
SET NOCOUNT ON;

-- Department Result Set
SELECT 
[DepartmentID]
,[Name]
,[GroupName] 
FROM [HumanResources].[Department]

-- Employee Result Set
SELECT 
[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
FROM [HumanResources].[Employee]

END

Result Set:

EXEC dbo.usp_MultipleResultSets WITH RESULT SETS

(

(

DepartmentID smallint,

Name dbo.Name,

GroupName dbo.Name

),

(

[BusinessEntityID] [int],

[NationalIDNumber] [nvarchar](15),

[LoginID] [nvarchar](256),

[OrganizationNode] [hierarchyid],

[JobTitle] [nvarchar](50),

[BirthDate] [date],

[MaritalStatus] [nchar](1),

[Gender] [nchar](1),

[HireDate] [date],

[SalariedFlag] [dbo].[Flag],

[VacationHours] [smallint],

[SickLeaveHours] [smallint],

[CurrentFlag] [dbo].[Flag]

)

)

Hope it helps! Happy TSQLing!