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

Dynamic SQL Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 9:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #862927
Posted Tuesday, March 16, 2010 2:24 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
I just picked the longest answer. I have to go back and learn how to do this right, I guess

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #884190
Posted Tuesday, April 6, 2010 6:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 3:53 AM
Points: 54, Visits: 15
we can also do it like.........


declare @sql varchar(100)
Set @sql = 'declare @cnt as int; Select @cnt=count(*) from ' + @tablevariable +'; SELECT @cnt'
Exec (@sql)

I think it was old question .bt i also want to contribute my ans.
Post #897473
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse