How To Alter A SQL Server Stored Procedure ?

T-SQL script example to alter an existing SQL Server stored procedure using ALTER PROCEDURE.

How to alter a SQL Server stored procedure already in a Microsoft database ? Simply use the alter procedure command instead of the create command to modify an existing SQL procedure. In this example let’s add the grouping on months and employees to sum up and group the data. Let’s also add a sorting option to have the Sales figures by descending amounts.

Video tutorial to Create and Alter a SQL Procedure

The video contains the same concepts from the blog post, but with simpler code examples.

Original T-SQL procedure to modify

This is the original script to create the stored procedure:

CREATE PROCEDURE uspListSales
AS
	SELECT *
	FROM dbo.SALES;
GO

And the original table used above is created using the following T-SQL script:

CREATE TABLE [dbo].[SALES] 
(
	[Year] INT, 
	[MonthName] NVARCHAR(50), 	-- alphanumeric
	[MonthCurrent] BIT, 		-- Boolean
	[NumberMonth] TINYINT, 		-- very small integer
	[EmployeeNumber] SMALLINT, 	-- small integer
	[NumberOfClients] INTEGER, 	-- integer
	[NumberOfSales] BIGINT, 		-- big integer
	[Amount_ET] NUMERIC(15,5), 	-- numeric
	[Amount_IT] DECIMAL(15,5) 	-- decimal
);

T-SQL script to Alter a Procedure

This T-SQL command modify the existing uspListSales stored procedure to get the sales for a given customer and to get the figures sorted by amounts from the more important to the less important one.

ALTER PROCEDURE uspListSales
AS
	SELECT 	
		MonthName, 
		EmployeeNumber, 
		SUM(NumberOfSales) 	AS SumNumberOfSales,
		SUM(AmountIT)		AS SumAmountIT
FROM dbo.SALES
GROUP BY 	MonthName,
		EmployeeNumber
ORDER BY 	SUM(AmountIT) DESC
GO

This command will modify the exiting object in order now to display the month name, the employee number, the total number of sales as well as total amount of sales including taxes.

The changes made to the stored procedure also sort the lines returned based on the total sales amounts in descending order. It means from the biggest sales amount to the smallest. And the total sales are grouped by month name and Employee number.

Enhance the Stored Procedure with a Return Value

A stored procedure in SQL Server can return an integer value to the calling environment, which can be extremely useful for indicating the status of the execution or the outcome of the operations carried out within the procedure. Now let’s modify the uspListSales stored procedure to include a return value that indicates the success or failure of the procedure execution.

Include a return value in the SQL Server procedure

To include a return value in the stored procedure, we need to declare a variable that will hold the status code and ensure that we end the procedure with a RETURN statement, which provides the value back to the caller.

ALTER PROCEDURE uspListSales
   @CustomerID INT,
   @StatusCode INT OUTPUT
AS
BEGIN
   BEGIN TRY
      -- Perform the grouped and sorted sales data retrieval
      SELECT    
         MonthName, 
         EmployeeNumber, 
         SUM(NumberOfSales) AS SumNumberOfSales,
         SUM(Amount_IT) AS SumAmountIT
      FROM dbo.SALES
      WHERE CustomerID = @CustomerID  -- Assuming there is a CustomerID field
      GROUP BY MonthName, EmployeeNumber
      ORDER BY SUM(Amount_IT) DESC

      -- If the query is successful, set the StatusCode to 0
      SET @StatusCode = 0
   END TRY
   BEGIN CATCH
      -- If an error occurs, set the StatusCode to the error number
      SET @StatusCode = ERROR_NUMBER()
   END CATCH

   RETURN @StatusCode
END
GO

In this enhanced version of the uspListSales stored procedure, we introduced :

• A parameter @CustomerID to filter the sales by customer
• An output parameter @StatusCode to capture the success or error status.
• The TRY … CATCH block is utilized to handle any errors that may occur during the execution of the procedure.

Manage the errors with a TRY CATCH block

Using this error management technique, we have 2 different cases managed here:

1. If the SELECT statement executes without any issues, the @StatusCode is set to 0, indicating success.
2. If an error occurs, the catch block captures the error number and sets it as the @StatusCode.

Conclusion

Modifying a SQL Server stored procedure using the ALTER PROCEDURE command is a straightforward task that can significantly enhance the functionality and efficiency of your database operations.

As explained and illustrated using the code, we can extend the capabilities of a stored procedure to not only perform more complex data retrieval and sorting operations but also to communicate execution status through a return value.

Implementing such best practices in database development can aid in creating robust, reliable, and scalable database applications. Now, to go further in the T-SQL learning journey, read this short tutorial about how to create a SQL Server function.