Create a stored procedure

Question

Create a stored procedure

Viewing 15 posts - 31 through 45 (of 64 total)

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

Toreador SSChampion Points: 11299 More actions · Answer 1

Stewart "Arturius" Campbell (10/15/2014)
Enough nitpicking.
yes, the question used a reserved keyword for a column name (purely by mishap, i'm sure).
but, despite that little inconvenience, the idea behind the question was good.
Hope to see more questions from you, Jaime

+1

SqlMel SSCrazy Points: 2891 More actions · Answer 2

It seems like we have another faulty question in our hands.

The correct answer should be "None of the above..." since you cannot use the keyword column as part of a select statement.

Points should definitely be awarded to everyone that chose that option as the correct answer.

---------------
Mel. 😎

Divine Flame SSCoach Points: 15943 More actions · Answer 3

Chose "none of the above" & got it worng!

However the correct answer is indeed not provided as option.

Sujeet Singh

SqlMel SSCrazy Points: 2891 More actions · Answer 4

Stewart "Arturius" Campbell (10/15/2014)
Enough nitpicking.
yes, the question used a reserved keyword for a column name (purely by mishap, i'm sure).
but, despite that little inconvenience, the idea behind the question was good.
Hope to see more questions from you, Jaime

I also hope to see more questions from him. That doesn't change the fact that the answer is wrong.

The idea behind the question was good, no question about it. The thing is that "none of the above" was one of the options given and it is the correct answer.

---------------
Mel. 😎

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 5

Vimal Lohani (10/14/2014)
happycat59 (10/14/2014)
I get error
Msg 156, Level 15, State 1, Procedure my_sp, Line 2
Incorrect syntax near the keyword 'FROM'.
because "Column" is a reserved keyword and needs to be delimited if you want to use it as a column (e.g. [Column]).
You are right,
Given answer is wrong, although syntax is right, Still Sql server will give error on given procedure.
Admin should review once again.

+1

edwardwill SSCertifiable Points: 5539 More actions · Answer 6

I'm amazed at the level of acceptance of sloppy, untested code here. Yes, it was only a test, and no, there was no real jeopardy. But it was two lines of SQL that threw an error, which had clearly not been run in a query window. In my book anyone thinking this is acceptable wouldn't get a look-in at my department.

stephen.long.1 SSCrazy Points: 2581 More actions · Answer 7

Stewart "Arturius" Campbell (10/15/2014)
Enough nitpicking.
yes, the question used a reserved keyword for a column name (purely by mishap, i'm sure).
but, despite that little inconvenience, the idea behind the question was good.
Hope to see more questions from you, Jaime

Nicely put, Stewart. I agree, using a reserved word for a column name was most likely an accident (and, I admit, it's something that I missed when I read the question :blush:). Should Jaime have tested this code? Sure. Does that negate the principle behind the question (teaching deferred name resolution)? Not at all. It certainly doesn't deserve all of the negative comments that have been generated.

Nice question, Jaime. I hope to see more questions from you in the future.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

Agree with the others that "none of the above" the correct answer is.

In fact the correct answer should have been "none of the above and below", since there was another option below. 😀

The explanation is not (completely) correct, since an important part was missing, which is mentioned in a note in the referred article

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

So the deferred name resolution only works for nonexistent table objects. If the table exists it will check the existence of the column.

Mike Hays SSCommitted Points: 1871 More actions · Answer 9

It is an interesting question, it just needed some testing before it was published.

stephen.long.1 SSCrazy Points: 2581 More actions · Answer 10

I think it's interesting to note that the article that Jaime linked to in BOL is only shown as being relevant to SQL Server 2005, 2008, and 2008 R2, even though 2012 and 2014 still have deferred name resolution. I know, it's shocking that BOL would be incomplete, right? :hehe:

TomThomson SSC Guru Points: 104773 More actions · Answer 11

As many have already pointed out, the "correct" answer is bunk. There will be a syntax error because "column" is a reserved word. So the only option that actually fits is "none of the above".

And now that SQLS 2005 is no longer in standard support it isn't appropriate to reference SQL 2005 ("(SQL.90)" in the url) documentation in the explanation for a question that doesn't specify 2005 explicitly.

But the idea behind the question was good, even though the execution was careless and the clear absence of checking is abysmal.

Tom

edwardwill SSCertifiable Points: 5539 More actions · Answer 12

TomThomson (10/15/2014)
As many have already pointed out, the "correct" answer is bunk. There will be a syntax error because "column" is a reserved word. So the only option that actually fits is "none of the above".
And now that SQLS 2005 is no longer in standard support it isn't appropriate to reference SQL 2005 ("(SQL.90)" in the url) documentation in the explanation for a question that doesn't specify 2005 explicitly.
But the idea behind the question was good, even though the execution was careless and the clear absence of checking is abysmal.

+1

JaimePC SSC Eights! Points: 864 More actions · Answer 13

Hello everybody

My apologize for this "mistake" over the QOTD, i really don't know what happened since the original query was:

CREATE PROCEDURE my_sp AS

SELECT columns FROM my_table

My intention was not set the keyword "COLUMN" but the word "COLUMNS" which let's this query runs perfectly on sql server, so the main idea was teach about the deferred name resolution.

Again, sorry for this but the most important thing is not breaking your ### good question in a row but learn something new about sql :-P:cool:

mluszczynski SSC Enthusiast Points: 171 More actions · Answer 14

Same issue as for happycat59:

Msg 156, Level 15, State 1, Procedure my_sp, Line 2

Incorrect syntax near the keyword 'FROM'.

So basically "none of the above" is the right answer 🙂 (but I can feel author's intention as with proper syntax this query should run without any error neither warning).

Really wish all QOTD tested before posting.

Mauricio_ SSCrazy Points: 2932 More actions · Answer 15

jaimepc199 (10/15/2014)
Hello everybody
My apologize for this "mistake" over the QOTD, i really don't know what happened since the original query was:
CREATE PROCEDURE my_sp AS
SELECT columns FROM my_table
My intention was not set the keyword "COLUMN" but the word "COLUMNS" which let's this query runs perfectly on sql server, so the main idea was teach about the deferred name resolution.
Again, sorry for this but the most important thing is not breaking your ### good question in a row but learn something new about sql :-P:cool:

Accepted. Everyone makes mistakes and s#@$t happens.