Create stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Randhir Singh » Create stored procedure

34 posts, Page 1 of 4

1 2 3 4 »»»

Create stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Randhir Singh

Posted Tuesday, March 15, 2011 8:40 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:46 PM
Points: 91, Visits: 328

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

Post #1078754

Nakul Vachhrajani

Posted Tuesday, March 15, 2011 11:38 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:56 AM
Points: 1,282, Visits: 1,613

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: @nakulv_sql
Google Plus: +Nakul

Post #1078788

Stewart "Arturius" Campbell

Posted Wednesday, March 16, 2011 12:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 3,164, Visits: 4,344

Interesting question.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1078807

Gobikannan

Posted Wednesday, March 16, 2011 2:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:31 AM
Points: 1,388, Visits: 462

Good question

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

Post #1078824

mohammed moinudheen

Posted Wednesday, March 16, 2011 2:39 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:29 PM
Points: 2,178, Visits: 3,599

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

Mohammed Moinudheen

Post #1078828

paul s-306273

Posted Wednesday, March 16, 2011 3:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:12 AM
Points: 1,167, Visits: 646

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

ronmoses

Posted Wednesday, March 16, 2011 5:39 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 11:49 AM
Points: 662, Visits: 699

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

Megistal

Posted Wednesday, March 16, 2011 6:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 1,253, Visits: 1,805

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

Scott Arendt

Posted Wednesday, March 16, 2011 6:57 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 3,063, Visits: 1,268

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

Bob Razumich

Posted Wednesday, March 16, 2011 7:01 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 6:14 AM
Points: 377, Visits: 894

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 »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions