Create stored procedure

  • Randhir Singh

    SSC Enthusiast

    Points: 164

    Comments posted to this topic are about the item Create stored procedure

  • Nakul Vachhrajani

    SSChampion

    Points: 10227

    I believe the reasons SQL Servers allows you to create the stored procedure are:

    1. Procedure names must comply with the naming standard of Identifiers (mentioned in the link provided by you). If we look at the standards for identifiers (http://msdn.microsoft.com/en-us/library/ms175874(v=SQL.90).aspx), we can see the following content:

    The first character must be one of the following:

    1. A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.

    2. The underscore (_), at sign (@), or number sign (#).

    ...

    2. The syntax for Stored Procedure creation is:

    CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

    [ { @parameter [ type_schema_name. ] data_type }

    [ VARYING ] [ = default ] [ OUT | OUTPUT ]

    ] [ ,...n ]

    [ WITH <procedure_option> [ ,...n ] ]

    [ FOR REPLICATION ]

    AS { <sql_statement> [;][ ...n ] | <method_specifier> }

    [;]

    The BEGIN...END fall under the <sql_statment>. Because a valid SQL Statement may also be a completely empty one, the system allows you to create a stored procedure.

    Hope this helps, and this was a good question!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • This was removed by the editor as SPAM

  • Gobikannan

    SSCrazy

    Points: 2735

    Good question

    -----------------
    Gobikannan

  • M&M

    SSC-Insane

    Points: 21699

    Thanks for the question. I had a feeling there is nothing wrong with this statement and chose the correct option. 🙂

    M&M

  • paul s-306273

    SSChampion

    Points: 10615

    I don't really see the value of this question...

    Also, from BOL:

    { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

    One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements. For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

    That states ONE OR MORE, not none or more.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    paul s-306273 (3/16/2011)


    I don't really see the value of this question...

    The nearly 40% of respondents who got it wrong learned something today. That alone seems valuable enough to me, especially since that's the entire point of QotD.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Megistal

    SSCrazy Eights

    Points: 8787

    There's even a compiled plan of that stored procedure! Which shows as empty.

    The requested actual plan from SSMS is empty and the estimated one give a 0 operator cost.

    Yet having an empty SP yield a 16k wasted resources for the cached plan.

    Always nice to know more.

  • Scott Arendt

    SSCertifiable

    Points: 7899

    ronmoses (3/16/2011)


    paul s-306273 (3/16/2011)


    I don't really see the value of this question...

    The nearly 40% of respondents who got it wrong learned something today. That alone seems valuable enough to me, especially since that's the entire point of QotD.

    Just because something is learned, does not necessarily mean that it has value. It only has value if it is useful. I would say that this is more interesting than useful.

  • Bob Razumich

    SSCrazy

    Points: 2043

    As one of the 40% who got this wrong (and learned from it) I'd say there are several advantages to a procedure like this:

    1. It's great for problem solving. If there are any issues on the database, this procedure can be immediately eliminated as a cause of any problem.

    2. It's perpetually easy to debug.

    3. The universality of the function allows granting full execute status to anyone with access to the database.

    4. One might call it a politically correct procedure because everyone can use it and no person (or database) should be offended by it.

    5. It can be a great source of entertainment as seasoned DBA's can initiate newbies by telling them to find out where the missing SQL went from the procedure. This is analagous to factory workers making new hires hunt for left handed wingnuts back in the day. Trust me, you've never lived till you spent a day looking for a left handed wingnut...

    Bob

    PS: I didn't mind getting it wrong. It sure made sense to me that an error should be generated for a sproc with no statements to execute. So much for logic. Thanks.

  • SanDroid

    SSChampion

    Points: 10068

    paul s-306273 (3/16/2011)


    { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

    The point of the question is about the minimum amount of code needed to create a Stored Procedure. Apparently 15% of the people responding do not know the answer. After checking online there is a question on the MS SQL certification exams that is almost word for word like this one.

    I liked the question! Of course I like most of the questions I get right! 😎

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    Scott Arendt (3/16/2011)


    ronmoses (3/16/2011)


    paul s-306273 (3/16/2011)


    I don't really see the value of this question...

    The nearly 40% of respondents who got it wrong learned something today. That alone seems valuable enough to me, especially since that's the entire point of QotD.

    Just because something is learned, does not necessarily mean that it has value. It only has value if it is useful. I would say that this is more interesting than useful.

    Value is in the eyes of the beholder! 😉

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    paul s-306273 (3/16/2011)


    I don't really see the value of this question...

    Also, from BOL:

    { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

    One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements. For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

    That states ONE OR MORE, not none or more.

    Another "funny" thing is that BEGIN...END does not define the body of the procedure. You can have multiple BEGIN...END in the body of a procedure, like this:

    create proc test

    as

    begin

    select 1

    end

    begin

    select 2

    endExecuting "test" will return two recordsets.

    A procedure can have an empty body, but BEGIN...END cannot be empty.

  • Bala'

    Ten Centuries

    Points: 1156

    Its not really adding any value..

  • Surii

    Default port

    Points: 1477

    Interesting question...

Viewing 15 posts - 1 through 15 (of 34 total)

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