Create stored procedure

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

    It's valuable if you learn what not to do or what might cause a problem. It's not always what is positively learned. sometimes it's what is negative that you should avoid.

  • Interesting.

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • There's always value in understanding the exceptions.

    The young man knows the rules, but the old man knows the exceptions. ~Oliver Wendell Holmes Sr.

  • Just don't forget to put the GO at the end, otherwise when you put it into an upgrade script you never know what you are going to get.

  • Thanks for the interesting question.

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

    This one is pretty interesting. I really didn't know that by adding Begin, End (twice or more), we can return 2 recordsets. But I dont think this type of proc can ever be used.

    Has anybody tried using sp_spaceused (returns 2 recordsets) in a select query ? Its just not possible.

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I agree with Ron.

    This question made us look into the basics of naming conventions.

    Thanks for the question.

  • Here's where it's useful. If you want an alternative to the DROP and CREATE method of maintaining stored procedure scripts - which would overwrite metadata about when a procedure was first created - you can use this:

    IF OBJECT_ID ( '[dbo].[NewProcedure]', 'P' ) IS NULL

    BEGIN

    EXEC ('CREATE PROCEDURE [dbo].[NewProcedure] AS')

    END

    GO

    ALTER PROCEDURE [dbo].[NewProcedure]

    -- Procedure code goes here

    Just an idea.

  • You can not use any special character as a first later of procedure name except _ .

  • amit_adarsh (3/17/2011)


    You can not use any special character as a first later of procedure name except _ .

    That is because they are not supported by the naming conventions for identifiers. Please go back to the 1st page of the discussion where I have explained why the SQL Server allows you to create this particular stored procedure.

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

    Follow me on
    Twitter: @sqltwins

  • ziangij (3/16/2011)I really didn't know that by adding Begin, End (twice or more), we can return 2 recordsets.

    You don't need multiple begin/end pairs to return 2 recordsets.

    create proc test

    as

    begin

    select 1

    select 2

    end

    or

    create proc test

    as

    select 1

    select 2

    will do just as well.

    We make regular use of procedures returning multiple recordsets. Using ADO you just use NextRecordSet to get at the data.

  • Bala' (3/16/2011)


    Its not really adding any value..

    There is actually quite a lot of value. When creating a system in a team environment, it allows the db developer to create all the procedure stubs initiallly even though they may not return any values. In turn, this allows the front end coders get on with coding the data access layer to an extent. The db developer can then continue addig the function bodies without other developers screaming at him to hurry up.

    You may not see the value, but it is there for somebody.

  • rjohal-500813 (3/17/2011)


    Bala' (3/16/2011)


    Its not really adding any value..

    You may not see the value, but it is there for somebody.

    😎 I would have to agree with that. 😉 Somebody out there sees all kinds of value in SQL developers and DBA's know the minimum amount of code required to create Procedures and Functions. Most likely why it is on the Certification exams.

Viewing 15 posts - 16 through 30 (of 34 total)

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