Dynamic SQL in a Transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic SQL in a Transaction

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2011-11-30 : 12:13:11

Is it possible to wrap a batch of dynamic SQL inside an explicit transaction with COMMIT or ROLLBACK depending on the value of @@ERROR? My dynamic sql is as follows:


BEGIN
	SET @Sql = 
	'update dbo.'+@Campaign+'
	set STATUS = ''U''
	where Order_Reference_Number = '''+@OrderNumber+''';
	'
	+
	'
	delete from dbo.'+@CBTable+' where i3_identity = '+cast(@Ident as varchar(255))+';
	insert dbo.'+@CBTable+' (workflowname, campaignname, siteid, i3_identity, schedtime, agentid, schedphone, schedccid)
	VALUES ('''+@CampaignType+''', '''+@CampaignType+''', '''', '+cast(@Ident as varchar(255))+', '''+cast(@CBDate as Varchar(30))+''', '''+@AgentName+''', '''+@Phonenum+''', -1);
	'
	+
	'
	update dbo.'+@Campaign+'
	set STATUS = ''S''
	where Order_Reference_Number = '''+@OrderNumber+''';
	'
--print @sql
EXECUTE sp_executesql @Sql
RETURN
END

Can this be done?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 12:17:28

sorry where's the transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

patshaw
Posting Yak Master

177 Posts

Posted - 2011-11-30 : 12:28:06

It's not there because that's my question. Is it possible and if so, would it function as expected and where should it be inserted?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 12:40:31

You have a choice.
You can put the transaction inside the dynamic sql or around the call.
You would need to put the error handling at the level at which you control the transaction otherwise you might get a transaction level mismatch

I would put it around the call
something like this - easy to test

begin try
begin tran
EXECUTE sp_executesql @Sql
commit tran
end try
if @@trancount > 0
rollback tran
raiserror ...
begin catch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

patshaw
Posting Yak Master

177 Posts

Posted - 2011-11-30 : 17:34:26

Many thanks Nigel and just the advice I was looking for. I'll test tomorrow.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-01 : 05:06:29

Check this example for transaction implementation in sql

http://msdn.microsoft.com/en-us/library/ms188929.aspx
http://stackoverflow.com/questions/4986683/difference-between-implicit-and-explicit-transaction

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-01 : 05:18:45

A better question is:

What are you going to do about sql injection attack?

Your code is potentially horribly unsafe. What cleaning of the input parameters are you doing?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

patshaw
Posting Yak Master

177 Posts

Posted - 2011-12-01 : 05:36:07

This is an in house application which calls the Stored Proc. There is no possibility for user manipulation within the app as it only presents pre-defined options to the user. With that in mind I feel confident that the code is safe from injection in this instance but thanks for highlighting this.

Kristen
Test

22859 Posts

Posted - 2011-12-01 : 10:17:04

Even with that proviso I, personally, think it would be much easier to write this to passe Parameters to sp_ExecuteSQL, rather than just giving isp_ExecuteSQL a parameter-concatenated SQL string to run.

That would also prevent you falling foul of things like a single ' in one of the @Variables, plus you wouldn't have to CAST the non-string variables, and so on.

Very likely one, or quite possibly two-or-more, orders of magnitude better performance too, if your query plans are likely to be reused.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources