Proper way to write a sproc

Question

Post reply

Proper way to write a sproc

erics44

SSCertifiable

Points: 6311
More actions
May 20, 2013 at 2:21 am

#277697

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

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 [/url]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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

...
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

erics44 SSCertifiable Points: 6311 More actions · Answer 3

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?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

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 :crying:

_____________________________________
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.

erics44 SSCertifiable Points: 6311 More actions · Answer 6

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 :crying:

ha, now now

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

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 [font="Arial Black"]loops [/font]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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)