Proper way to write a sproc

  • 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

  • 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

  • ...

    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]

  • 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?

  • 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]

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

  • 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)

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

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