SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Begin Tran...Commit Tran with Exec SQL Insert Cmd


Begin Tran...Commit Tran with Exec SQL Insert Cmd

Author
Message
wmendoza-1114809
wmendoza-1114809
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 66
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.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17219 Visits: 7418
1 - instead of using EXEC(@Sql), rather declare your @Sql as NVARCHAR and use EXEC sp_executesql @Sql
2 - wrap the exec in a BEGIN TRY .. END TRY block and trap the @@Error value in the BEGIN CATCH ... END CATCH block (the @@Error value will be passed back to the calling / executing command)

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9148 Visits: 8492
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!


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
wmendoza-1114809
wmendoza-1114809
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 66
MANY THANKS for your replies.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search