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.
Table of Contents
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:
- If the SELECT statement executes without any issues, the @StatusCode is set to 0, indicating success.
- If an error occurs, the catch block captures the error number and sets it as the @StatusCode.
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.