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
