Tom.Thomson (2/9/2010)
Lynn Pettis (2/9/2010)
Explain to me why you think each of the answers is wrong.Because each answer tries to select from a table with a particular name in an completely unspecified database. That table probably doesn't exist in most databases (it doesn't exist in any DB on any server I ever controlled - and I'll leave you to guess whether that means that I never controlled an HR or Payroll DB or that the table just happened to be named "seirbhiseach" or "Angestellte" instead of "employee".
In the real world it's no good doing what the commenter to whom I was replying suggested - just pick any database you have and then pick any table you have in that DB and then make the query refer to that table - whoever produced the requirement had something in mind, and surely it wasn't to let the developer pick the database and table at random. OK, so here we are talking about QOD instead of about real requirements, and the standard of questions is sometimes pretty low so adjusting the question might often make sense - but as a general rule I think we should try to treat the questions as if they are hard and fast, not subject to adjustment without obtaining agreement from the poser, because in the real world all the badly formulated (and often completely wrong-headed) requirements we see have to go through that negotiate with the poser process.
First, you are operating under a false assumption that the QotD is based on a real world situation.
The purpose of the QotD is to ask a question that will normally test your knowledge about some aspect of SQL Server. I say normally, as there have been questions of a humorous sort asked at times.
This purpose of this question was to test your knowledge of how you would return a value from a dynamic query.
To dismiss the correct answer, C, simply because the poster failed to specify a database, or for that matter a table that may exist on everyone's system is simply ludicrous.
In this case, the poster could have used a SandBox database, created a table called dbo.Employees, inserted 1 row of data, and then tested each possible answer to determine the output. Which, by the way, is exactly what I just did.
Answer C is the code that works.
Answer A returns null, but that is because @sql is null. The variable @cnt was declared, but never initialized prior to being used in the concatenation. Concat a string with a null, you get a null.
Answer B is wrong, not because the code in @sql is wrong (it is actually valid), but because of how it is being used in an attempt to set the value of @cnt. That is invalid sql code.
Answer C is correct, because it builds each string that will be used in the sp_executesql procedure properly. If you take that code, change the value of the variable @tablevariable to contain the valid name of a table in your database and run that code in that database, you will get an answer.