Alter SQL Server stored 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.

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.