August 11, 2015 at 4:02 am
Hi there,
I'm trying to make a patch script and I wanted this kind of behavior
set xact_abort on
BEGIN TRAN
BEGIN TRY
<some sql script>
GO
CREATE PROCEDURE dbo.SampleProcedure
<Procedure contents>
GO
<some sql script>
END TRY
BEGIN CATCH
<some sql script>
END CATCH
COMMIT TRAN
set xact_abort off
🙁 The problem is that "GO" is not allowed in TRY CATCH blocks (but allowed in Transaction blocks), and creation of some objects like stored procedures needs to end the previous code/script batch with "GO". I already tried replacing "GO" with ";" but it doesnt allow me to create the procedure.
Although this script can already rollback my changes, I wan't to handle the error a bit more.
Any ideas or other implementation in your mind?
I hope you can help me with this. Thanks in advance! 🙂
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 11, 2015 at 6:46 am
Sorry, but TRY / CATCH blocks cannot, under any circumstances, go beyond the "batch". GO is a batch terminator, and there's just no getting around the fact that stored procedures require their own batch, so nope, you can't do that. You'd have to use some kind of scripted program, whether it's a .NET Windows Forms App, or a VBScript, or an SSIS Script Task, to accomplish this via individual batches, with the logic under the control of your script. Yes, it's a lot more work.
I can't help but wonder why creating a stored procedure needs to be conditional. Seems to me that executing should be the conditional thing. If there are security concerns in having it exist, then lock it down to execution only by users within a particular AD group. Generally, desiging a sproc to be conditionally created just doesn't really accomplish very much. What is your objective, and why do you need a conditional sproc creation?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 11, 2015 at 7:00 am
If you really need to do this you will have to move the create procedure code to dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 7:10 am
Well actually :hehe: just a bit for some aesthetics, hahaha. yeah, not really important.
See, my boss liked how my script produces print outputs, and having the default SQL error output... I was thinking of changing how the error was being printed out.
Plus, I'm just also curious if there was actually a way XD :hehe:
Thanks
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 11, 2015 at 7:12 am
Yeah, I already thought of dynamic sql, but didn't like the idea since my procedure is a long one and it'll make the script less readable and harder to update 🙂
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 11, 2015 at 7:18 am
Quatrei.X (8/11/2015)
Yeah, I already thought of dynamic sql, but didn't like the idea since my procedure is a long one and it'll make the script less readable and harder to update 🙂
It works but you pointed the major downside of it. Maintainability is a nightmare.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 7:26 am
It works but you pointed the major downside of it. Maintainability is a nightmare.
Agree XD
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply