Skip to main content
Removed superfluous stuff only serving to generate scrollbars
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619
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 
CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);

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 kerfluffled: when I purposely enter the wrong name for the first ALTER 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 
CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql   
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable (FirstName, LastName) EXEC(@SQL)
END
GO 

CREATE PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql   
  SET @SQL = 'SELECT FirstName, LastName '
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)
END
GO   

Now that the procedures exist, here's what has me kerfuffled: when I purposely enter the wrong name for the first ALTER 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 

ALTER PROCEDURE ProcessReport2  -- this procedure does not exist, causing an error
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql   
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable(FirstName, LastName) EXEC(@SQL)
END
GO
ALTER PROCEDURE RetrieveReport
AS
BEGIN
  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 '
  EXEC (@SQL)

END
GO 

IF @@ERROR = 0
  COMMIT TRANSACTION;
ELSE
  ROLLBACK TRANSACTION;
Improved formatting
Source Link

Adds a row to 2 different tables, provided the values aren't already there Drops a column if it exists Adds a column if it doesn't exist Alters 2 stored procedures that generate dynamic SQL to build a report query

  1. Adds a row to 2 different tables, provided the values aren't already there
  2. Drops a column if it exists
  3. Adds a column if it doesn't exist
  4. Alters 2 stored procedures that generate dynamic SQL to build a report query

Adds a row to 2 different tables, provided the values aren't already there Drops a column if it exists Adds a column if it doesn't exist Alters 2 stored procedures that generate dynamic SQL to build a report query

  1. Adds a row to 2 different tables, provided the values aren't already there
  2. Drops a column if it exists
  3. Adds a column if it doesn't exist
  4. Alters 2 stored procedures that generate dynamic SQL to build a report query
added 2 characters in body; edited tags
Source Link
Kin Shah
  • 62.4k
  • 6
  • 122
  • 240

Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2 Invalid object name 'ProcessReport2'. Msg 3902, Level 16, State 1, Line 3 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2 Invalid object name 'ProcessReport2'.

Msg 3902, Level 16, State 1, Line 3 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2 Invalid object name 'ProcessReport2'. Msg 3902, Level 16, State 1, Line 3 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2 Invalid object name 'ProcessReport2'.

Msg 3902, Level 16, State 1, Line 3 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Source Link
Loading