DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use ALTER PROCEDURE?

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

*/

------------

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE