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 1234»»»

Create stored procedure Expand / Collapse
Author
Message
Posted Tuesday, March 15, 2011 8:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 4:04 AM
Points: 100, Visits: 339
Comments posted to this topic are about the item Create stored procedure
Post #1078754
Posted Tuesday, March 15, 2011 11:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 1,403, Visits: 1,808
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1078788
Posted Wednesday, March 16, 2011 12:42 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 @ 9:37 AM
Points: 3,856, Visits: 4,996
Interesting question.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1078807
Posted Wednesday, March 16, 2011 2:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:43 AM
Points: 1,822, Visits: 566
Good question

-----------------
Gobikannan
Post #1078824
Posted Wednesday, March 16, 2011 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:21 PM
Points: 2,270, Visits: 3,780
Thanks for the question. I had a feeling there is nothing wrong with this statement and chose the correct option.

Mohammed Moinudheen
Post #1078828
Posted Wednesday, March 16, 2011 3:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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.

Post #1078833
Posted Wednesday, March 16, 2011 5:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 846, Visits: 850
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
Post #1078868
Posted Wednesday, March 16, 2011 6:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 1,407, Visits: 2,073
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.
Post #1078890
Posted Wednesday, March 16, 2011 6:57 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: Yesterday @ 8:03 AM
Points: 3,442, Visits: 1,473
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.
Post #1078914
Posted Wednesday, March 16, 2011 7:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 419, Visits: 977
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.
Post #1078916
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse