CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
-- start creating dynamic sql
SET @SQL = 'SELECT FirstName, LastName, '
--create rest of dynamic SQL here...
PRINT @SQL
INSERT INTO ReportTable
(FirstName, LastName)
EXEC(@SQL)
END
GO
CREATE PROCEDURE RetrieveReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
-- start creating dynamic sql
SET @SQL = 'SELECT FirstName, LastName '
--create rest of dynamic SQL here...
SET @SQL = @SQL + ' FROM ReportTable '
PRINT @SQL
EXEC (@SQL)
END
GO
Now that the procedures exist, here's what has me kerfluffledkerfuffled: when I purposely enter the wrong name for the first ALTER PROCEDUREALTER PROCEDURE
, the edits I made to the second procedure are completed. Below is the short version of the transaction I've written:
BEGIN TRANSACTION
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ProcessReport2 -- this procedure does not exist, causing an error
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
-- start creating dynamic sql
SET @SQL = 'SELECT FirstName, LastName, '
--create rest of dynamic SQL here...
PRINT @SQL
INSERT INTO ReportTable
(FirstName, LastName)
EXEC(@SQL)
END
GO
ALTER PROCEDURE RetrieveReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
-- start creating dynamic sql
SET @SQL = 'SELECT DISTINCT FirstName, LastName ' --DISTINCT added
--create rest of dynamic SQL here...
SET @SQL = @SQL + ' FROM ReportTable '
PRINT @SQL
EXEC (@SQL)
END
GO
IF @@ERROR = 0
COMMIT TRANSACTION;
ELSE
ROLLBACK TRANSACTION;
GO