Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Proper way to write a sproc Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 2:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:43 AM
Points: 281, Visits: 561
Hi
I am an experienced sql developer and have written many stored procedures but i am worried that i am still quite beginner when setting them up.

i start my sproc and then get straight into writing the code that performs the tasks that the sproc needs to do, declaring the variables queries loops what ever.

i look at some other procs that people have done (and sorry i dont hvae any examples) and there are all sorts of other commands that come in first.

should i really be doing things before i start the nitty gritty?

Thanks in advance
Post #1454432
Posted Monday, May 20, 2013 2:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 3,018, Visits: 3,210
Have you got any examples of the other commands that you are talking about?

There are many valid reasons why a stored procedure wouldn't conform to

Alter procedure Dummy as

Declare variables


Run code





-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1454435
Posted Monday, May 20, 2013 3:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

...
i look at some other procs that people have done (and sorry i dont hvae any examples) and there are all sorts of other commands that come in first.
...

What other commands are you taking about?

SET NOCOUNT ON ?

Anything else?




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1454455
Posted Monday, May 20, 2013 4:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:43 AM
Points: 281, Visits: 561
thanks for the replies

Heres an example of the types of thing that im talking about, this is a part of a script that someone had set up and sent to me.

I cant find an example in a sproc at the minute, ill keep looking :)

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT


is this over the top?
Post #1454471
Posted Monday, May 20, 2013 4:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Are these statements used inside of proc code or outside?
If the later, then I guess that developer who sent you this code just scripted something in SSMS and used it as his template for creating stored procedures.

If these statements are inside of proc, than probably they required to achieve some sort of functionality.
I guess you know what BEGIN TRANSACTION and COMMIT are for?
Each of the listed SET commands changes specific connection setting, You can refer to BoL for details.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1454478
Posted Monday, May 20, 2013 7:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
Eugene Elutin (5/20/2013)
I guess you know what BEGIN TRANSACTION and COMMIT are for?


i wouldn't be that sure about it, Eugene


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1454518
Posted Monday, May 20, 2013 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:43 AM
Points: 281, Visits: 561
PaulB-TheOneAndOnly (5/20/2013)
Eugene Elutin (5/20/2013)
I guess you know what BEGIN TRANSACTION and COMMIT are for?


i wouldn't be that sure about it, Eugene


ha, now now


Post #1454527
Posted Monday, May 20, 2013 3:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
erics44 (5/20/2013)
i start my sproc and then get straight into writing the code that performs the tasks that the sproc needs to do, declaring the variables queries loops what ever.


The first thing that I'd learn is the paradigm shift necessary to avoid 99.9% of all explicit loops and some of the hidden RBAR methods. See my signature line below for definitions of both as a starting point.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse