Sequence of events inside a stored procedure

Clive D Pottinger

Hello everyone. Everything I have read says that operations within a stored procedure are executed in the order in which they are encountered, and the semi-colons and BEGIN-END blocks can be used to ensure that statements are executed before subsequent statements are performed. However I am stumped by what is happening in my script. The intent of the script is to copy an order record and all associated table entries for the order from one database to another. I have stripped down the script code below to just the order table itself (and removed some passwords, server names etc). Here is the code:

IF @@SERVERNAME <> 'Server1'
BEGIN
EXEC SP_ADDLINKEDSERVER
'Server1'

  EXEC SP\_ADDLINKEDSRVLOGIN
    'Server1',
    'False',
    NULL,
    'userX',
    'passwordX'

END

go

DECLARE @mode VARCHAR(1);
DECLARE @orderNum INT;

SET @orderNum = 1338464;
SET @mode = 'R' -- set to 'R' for Report or 'U' for update
BEGIN TRANSACTION

DECLARE @sourceName VARCHAR(8);
DECLARE @okay CHAR(1);
DECLARE @user VARCHAR(20);

SET @sourceName = 'liveDB';
SET @user = SYSTEM_USER;

CREATE synonym sourcesummary FOR [Server1].[liveDB].[dbo].[order_summary];
SET @okay = 'Y'

IF @okay = 'Y'
AND @@SERVERNAME = 'Server1'
BEGIN
SELECT 'Cannot execute on live server - retry on the sever hosting the db that is to be copied to' [error]

  SET @okay = 'N'

END

IF @okay = 'Y'
AND NOT EXISTS (SELECT order_num
FROM sourcesummary
WHERE order_num = @orderNum)
BEGIN
SELECT 'Unable to find order' [error],@sourceName [source db],@orderNum [order number];

  SET @okay = 'N';

END

IF @okay = 'Y'
BEGIN
IF EXISTS (SELECT order_num
FROM order_summary
WHERE ordr_num = @orderNum)
BEGIN
PRINT 'deleting order_summary:';

        DELETE order\_summary
        WHERE  order\_num = @orderNum;
    END

  BEGIN

  PRINT 'copying order\_summary:';
      INSERT INTO order\_summary
      SELECT \[order\_num\],\[order\_date\], etc, etc, etc...
      FROM   sourcesummary
      WHERE  sourcesummary.order\_num = @orderNum;
  END

  PRINT 'retreiving copied order\_summary:';
  SELECT 'copied' \[order\_summary\],\*
  FROM   order\_summary
  WHERE  order\_num = @orderNum;

END

DROP synonym sourcesummary;

IF @mode = 'U'
BEGIN
COMMIT TRANSACTION

  SELECT 'Changes hav

Andrei Straut

Try committing the transaction after deleting and inserting the rows into the database, but before retrieving them, I'm assuming that's what causing the problem

Full-fledged Java/.NET lover, full-fledged PHP hater. Full-fledged Google/Microsoft lover, full-fledged Apple hater. Full-fledged Skype lover, full-fledged YM hater.

PIEBALDconsult

Or add the WITH(NOLOCK) hint. I also recall others having similar trouble when using GO, but that doesn't appear to be the problem here.

Clive D Pottinger

Thank you gentlemen. I found the cause of the issue. The section that reads

  PRINT 'copying order\_summary:';
      INSERT INTO order\_summary
      SELECT \[order\_num\],\[order\_date\], etc, etc, etc...
      FROM   sourcesummary
      WHERE  sourcesummary.order\_num = @orderNum;

should have been

  PRINT 'copying order\_summary:';
      SET IDENTITY\_INSERT order\_summary ON;
      INSERT INTO order\_summary (\[order\_num\],\[order\_date\], etc, etc, etc...)
      SELECT \[order\_num\],\[order\_date\], etc, etc, etc...
      FROM   sourcesummary
      WHERE  sourcesummary.order\_num = @orderNum;
      SET IDENTITY\_INSERT order\_summary OFF;

"... to become an SQL expert? Oh, I figure 3 or 4 days reading. A week tops!"

Clive Pottinger Victoria, BC

cbeglobal

:thumbsup: