June 17, 2010 at 11:51 am
For my education, I EXEC a dynamic sql that is embedded in a BEGIN TRAN ... COMMIT TRAN. A simple example and assume the var have been defined:
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO dbo.TARGET_TBL'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM dbo.SOURCVE_TBL'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
@sqlcmd has the name of the target table in a string which the outer layer, BEGIN TRAN ... COMMIT TRAN may not see.
Question1: Will COMMIT TRAN know to commit the changes to the SOURCE_TBL? (executed dynamically). What about ROLLBACK?
Question2: I will receive the @@ERROR number if it fails?
Excuse my unawareness and MANY THANKS.
June 18, 2010 at 4:08 am
This was removed by the editor as SPAM
June 18, 2010 at 4:08 am
Well, lets see shall we? 😉
USE TestingDB
DECLARE @sqlcmd AS VARCHAR(4000)
DECLARE @theErrNbr AS VARCHAR(500)
CREATE TABLE table1 (
colA VARCHAR(50),
colB VARCHAR(50))
CREATE TABLE table2 (
Acol VARCHAR(50),
Bcol VARCHAR(50))
INSERT INTO table2(Acol,Bcol)
SELECT 'Jack','Jill'
UNION ALL SELECT 'Jack','Beanstalk'
UNION ALL SELECT 'Queen','Hearts'
--Working version
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
/*Output
colA colB
-------------------------------------------------- --------------------------------------------------
Jack Jill
Jack Beanstalk
Queen Hearts
ERROR
--------------------------------------------------
0
*/
--Cleanup
DROP TABLE table1
CREATE TABLE table1 (
colA VARCHAR(50),
colB VARCHAR(50))
DROP TABLE table2
--Not working version due to lack of table2
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
--Cleanup
DROP TABLE table1
/*Ouput
Msg 208, Level 16, State 1, Line 1
Invalid object name 'table2'.
colA colB
-------------------------------------------------- --------------------------------------------------
ERROR
--------------------------------------------------
208
*/
Hope that answers your question!
June 18, 2010 at 9:55 am
MANY THANKS for your replies.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply