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.
How to alter a SQL Server stored 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
The stored procedure group and sort the sales amount
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.
This is the original script to create the stored procedure:
CREATE PROCEDURE uspListSales AS SELECT * FROM dbo.SALES; GO
And the original table used 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 );
To go further in the T-SQL learning journey, read this short tutorial about how to create a SQL Server function.