|
Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the use of ALTER PROCEDURE STATEMENT. First a stored procedure is created and tested, then altered and tested.
-- SQL drop procedure if exists only - drop stored procedure
USE AdventureWorks2008;
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sprocSalesByStaffByFiscalYears]')
AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE [dbo].[sprocSalesByStaffByFiscalYears]
GO
-- MSSQL stored procedure create - no parameter
-- Microsoft SQL Server T-SQL pivot crosstab query
CREATE PROCEDURE sprocSalesByStaffByFiscalYears
AS
SELECT [pivot].[FullName],
[pivot].[JobTitle],
[pivot].[SalesTerritory],
COALESCE([pivot].[2003],0.0) AS [2003],
COALESCE([pivot].[2004],0.0) AS [2004]
FROM (SELECT soh.[SalesPersonID],
p.[FirstName] + ' ' + p.[LastName] AS [FullName],
e.[JobTitle],
st.[Name] AS [SalesTerritory],
soh.[SubTotal],
YEAR(DATEADD(m,6,soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[BusinessEntityID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = sp.[BusinessEntityID]) AS soh
PIVOT
(SUM([SubTotal])
FOR [FiscalYear] IN ( [2003],[2004] ) ) AS [pivot];
GO
-- T-SQL stored procedure execution
EXEC sprocSalesByStaffByFiscalYears
GO
/* Partial results
FullName JobTitle SalesTerritory 2003 2004
Michael Blythe Sales… Northeast 4743906.8935 4557045.0459
Linda Mitchell Sales… Southwest 4647225.4431 5200475.2311
*/
-- SQL Server alter procedure - alter stored procedure
-- T-SQL currency formatting
ALTER PROCEDURE sprocSalesByStaffByFiscalYears
AS
SELECT [pivot].[FullName],
[pivot].[SalesTerritory],
COALESCE('$' + convert(VARCHAR,[pivot].[2003],1),'') AS [2003],
COALESCE('$' + convert(VARCHAR,[pivot].[2004],1),'') AS [2004]
FROM (SELECT soh.[SalesPersonID],
p.[FirstName] + ' ' + p.[LastName] AS [FullName],
e.[JobTitle],
st.[Name] AS [SalesTerritory],
soh.[SubTotal],
YEAR(DATEADD(m,6,soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[BusinessEntityID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = sp.[BusinessEntityID]) AS soh
PIVOT
(SUM([SubTotal])
FOR [FiscalYear] IN ( [2003],[2004] ) ) AS [pivot]
ORDER BY FullName;
GO
-- Execute stored procedure after ALTER
EXEC sprocSalesByStaffByFiscalYears
GO
/* Results
FullName SalesTerritory 2003 2004
David Campbell Northwest $1,377,431.33 $1,930,885.56
Garrett Vargas Canada $1,480,136.01 $1,764,938.99
Jae Pak United Kingdom $5,287,044.31 $5,015,682.38
Jillian Carson Central $4,991,867.71 $3,857,163.63
José Saraiva Canada $1,488,793.34 $3,189,356.25
Linda Mitchell Southwest $4,647,225.44 $5,200,475.23
Lynn Tsoflias Australia $1,758,385.93
Michael Blythe Northeast $4,743,906.89 $4,557,045.05
Pamela Ansman-Wolfe Northwest $900,368.58 $1,656,492.86
Rachel Valdez Germany $2,241,204.04
Ranjit Varkey Chudukatil France $1,677,652.44 $3,827,950.24
Shu Ito Southwest $2,870,320.86 $3,018,725.49
Tete Mensa-Annan Northwest $883,338.71 $1,931,620.18
Tsvi Reiter Southeast $2,661,156.24 $2,811,012.72
*/
------------
|
