March 15, 2011 at 8:40 pm
Comments posted to this topic are about the item Create stored procedure
March 15, 2011 at 11:38 pm
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
Follow me on
Twitter: @sqltwins
March 16, 2011 at 12:42 am
This was removed by the editor as SPAM
March 16, 2011 at 2:20 am
Good question
-----------------
Gobikannan
March 16, 2011 at 2:39 am
Thanks for the question. I had a feeling there is nothing wrong with this statement and chose the correct option. 🙂
M&M
March 16, 2011 at 3:38 am
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.
March 16, 2011 at 5:39 am
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
March 16, 2011 at 6:16 am
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.
March 16, 2011 at 6:57 am
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.
March 16, 2011 at 7:01 am
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.
March 16, 2011 at 7:09 am
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! 😎
March 16, 2011 at 7:13 am
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! 😉
March 16, 2011 at 7:48 am
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.
March 16, 2011 at 7:48 am
Its not really adding any value..
March 16, 2011 at 7:59 am
Interesting question...
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply