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.
| Author |
Topic |
|
LearnEveryDay
Starting Member
37 Posts |
 Posted - 2005-06-17 : 06:56:53
|
| Hello Everyone,I am using sql tansaction for my stored procedures.For the best practice I always use SET NOCOUNT ONBEGIN TRANACTION-----HEAR GOES ALL MY QUERY STUFFIF @@ERROR = 0 COMMIT TRANSACTIONELSE ROLLBACK TRANSACTIONBut I have told recently that I should not use sql transaction for SELECT statments, It should be used in the stored procedure which has got INSERT, UPDATE and DELETE statements.But I have written thousands of sps with that, it is very difficult to remove it now My question is, Is it wrong to use sql transacation for SELECT statements?What will happen if leave that transaction in the sps which has got SELECT statement?Cheers,LearnEveryDay |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-17 : 07:07:20
|
| A transaction adds nothing but overhead if all you have are SELECT statements. You're not alone, a lot of people use transactions unnecessarily. I think it's just a reflex to add it without truly determining if it's needed.Script out your stored procedures to files (which is the proper way to manage them anyway), do a search and replace, then run these scripts to recreate the procedures in SQL Server. |
 |
|
|
LearnEveryDay
Starting Member
37 Posts |
Posted - 2005-06-17 : 07:18:07
|
| Thanks robvolk, I will try to do that.Cheers |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-17 : 07:54:30
|
quote:
You're not alone, a lot of people use transactions unnecessarily.
Case in point:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51244Amanda doesn't know it, but what she's doing completely violates the concept of a transaction, so there's not much point in having it at all.It also sounds like she's letting a separate application handle the transaction, probably via ADO, another very common thing that causes a lot of grief. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-17 : 09:03:48
|
Rob could you please give some indication of what you mean by this quote:
Script out your stored procedures to files (which is the proper way to manage them anyway)
I have most of my stored procedures in scripts but have recently changed from having a lot of them in one script to having each procedure in one script. I sort of feel that this is better but it means that I have files all over the place, which I see as a bit of a risk. Not helped by the fact that I have no working version control software (long story).I'd be interested to know what others thinksteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-17 : 13:55:36
|
| I keep each sproc in its own script file. It makes it easier to know exactly what you're changing, and prevent you from changing something you don't want to. Doing a search and replace on one big script file is a good way to hurt yourself if you're not really, really careful.I also keep the files either in one folder or a small number of folders. Our SourceSafe tree has categories of folders for our sproc scripts so we can group them together. We can get them or check them out recursively and it works pretty well. We have about 800 sprocs and I've never really had a problem finding or managing them with this structure. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-20 : 04:21:31
|
| Cheers, thanks Rob.steveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
|
|
|
|
|
Sql Transaction