SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sudhir Chawla / Dynamic SQL / Latest Posts

RE: Dynamic SQL

preeti.singh — Tue, 06 Apr 2010 06:21:13 GMT

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.:-)

RE: Dynamic SQL

Peter Trast — Tue, 16 Mar 2010 14:24:29 GMT

I just picked the longest answer. I have to go back and learn how to do this right, I guess :w00t:

RE: Dynamic SQL

Lynn Pettis — Tue, 09 Feb 2010 21:45:46 GMT

[quote][b]Tom.Thomson (2/9/2010)[/b][hr][quote][b]Lynn Pettis (2/9/2010)[/b][hr]Explain to me why you think each of the answers is wrong.[/quote]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.[/quote]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.

RE: Dynamic SQL

L' Eomot Inversé — Tue, 09 Feb 2010 20:40:13 GMT

[quote][b]Lynn Pettis (2/9/2010)[/b][hr]Explain to me why you think each of the answers is wrong.[/quote]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.

RE: Dynamic SQL

L' Eomot Inversé — Tue, 09 Feb 2010 20:06:43 GMT

[quote][b]Lynn Pettis (2/9/2010)[/b][hr]Okay, explain to me why C, although the correct answer, is wrong.[/quote]Becasue all the code options povide produce an error or NULL. Now if you think that an error or NULL is the expected result, then one of them may be right and it might be C. I thought that the expected result was some useful data, not an error message or NULL, so that none of the suggested answers produced the expected result. I may have been wrong - maybe case C produced the expected error message, in which case I have to ask why that error message rather than the result of A or B is the expected result. Do you have an answer for that? Or are you taking the rather arrogant view that because you don't like the question you will reformulate it to mean something different (perhaps something more fully specified) rather than refer it back to the poser?

RE: Dynamic SQL

Lynn Pettis — Tue, 09 Feb 2010 19:41:12 GMT

[quote][b]Tom.Thomson (2/9/2010)[/b][hr][quote][b]Rupal Raval (1/5/2010)[/b][hr]what error does it throw?have you written 'declare @tablevariable varchar(100)set @tablevariable = 'any valid table name'Before code C?map @tablevariable to one of your database table, select that database as current database and then execute the code[/quote]No, and it wouldn't help if I did. I don't have a table called 'any valid table name' in any database on my server. (I know that sounds nasty - but the provocation was extreme!)Claiming that this is a sensible question with a sensible answer because it's obvious that unless you change the question all the answers are wrong is just nonsense. Yes, there are real life situations like that (the requirement as provided is nonsense), but in those situations you should not guess what part of the question (requirement) you have to change, you should throw the thing back at whoever provided the question and point out that it's nonsense. Otherwise you risk finding yourself spending a lot of effort on doing something completely useless, because you guessed wrong. And in practise, no matter how obvious it looks to you what the required modification is, you are NOT the person who understands the business inside out and specifies the requirement, so your guess will often be wrong. (If you were the person with that knowledge and understanding, you would be writing the requirement, and would not make silly errors like that - I hope.)[/quote]Explain to me why you think each of the answers is wrong.

RE: Dynamic SQL

Lynn Pettis — Tue, 09 Feb 2010 19:40:01 GMT

[quote][b]Tom.Thomson (2/9/2010)[/b][hr]OK, I answered C because A and B are so completely wrong that C is the only option where we can reasonably guess that the setter of the question got it wrong enough to think C would work. So I got my point - for understanding the sort of stupid error that the question setter would make, not for producing a correct answer (because "(D) none o the above" is the correct answer, but not available). This is a really bloody awful question and answer.I'm seeing far too much of thios sort or thing in QOD. Presumably it's completely unmoderated?[/quote]Okay, explain to me why C, although the correct answer, is wrong.

RE: Dynamic SQL

L' Eomot Inversé — Tue, 09 Feb 2010 19:30:05 GMT

[quote][b]Rupal Raval (1/5/2010)[/b][hr]what error does it throw?have you written 'declare @tablevariable varchar(100)set @tablevariable = 'any valid table name'Before code C?map @tablevariable to one of your database table, select that database as current database and then execute the code[/quote]No, and it wouldn't help if I did. I don't have a table called 'any valid table name' in any database on my server. (I know that sounds nasty - but the provocation was extreme!)Claiming that this is a sensible question with a sensible answer because it's obvious that unless you change the question all the answers are wrong is just nonsense. Yes, there are real life situations like that (the requirement as provided is nonsense), but in those situations you should not guess what part of the question (requirement) you have to change, you should throw the thing back at whoever provided the question and point out that it's nonsense. Otherwise you risk finding yourself spending a lot of effort on doing something completely useless, because you guessed wrong. And in practise, no matter how obvious it looks to you what the required modification is, you are NOT the person who understands the business inside out and specifies the requirement, so your guess will often be wrong. (If you were the person with that knowledge and understanding, you would be writing the requirement, and would not make silly errors like that - I hope.)

RE: Dynamic SQL

L' Eomot Inversé — Tue, 09 Feb 2010 19:18:14 GMT

OK, I answered C because A and B are so completely wrong that C is the only option where we can reasonably guess that the setter of the question got it wrong enough to think C would work. So I got my point - for understanding the sort of stupid error that the question setter would make, not for producing a correct answer (because "(D) none o the above" is the correct answer, but not available). This is a really bloody awful question and answer.I'm seeing far too much of thios sort or thing in QOD. Presumably it's completely unmoderated?

RE: Dynamic SQL

JF1081 — Thu, 14 Jan 2010 08:01:15 GMT

[quote][b]Steve Eckhart (1/5/2010)[/b][hr]Like others, I answered A because it returned "the expected output": NULL. Option C returns an error:[/quote]I am guilty of copying and pasting when I do not know an answer, but I don't get mad if I don't get my points becasue I obviously didn't know the answer anyway.

RE: Dynamic SQL

Dan Guzman - Not the MVP — Wed, 06 Jan 2010 09:51:21 GMT

I have to laugh, After seeing all these comments about what should have been implied and what was inferred, I can't tell you the number of times these QotD have been trick questions, trying to 'get you' with slight and small syntax errors or db assumptions, intentionally generating an error message.Just because someone ran the code and got an error message does not mean the error message was not the correct answer.In this case, an error message was not option 'D', so I proceeded with the assumption that at least one of the three generated correct results.Thanks for the question Sudhir. I enjoyed this one.

RE: Dynamic SQL

Lynn Pettis — Wed, 06 Jan 2010 06:32:02 GMT

[quote][b]john.curran.z3g (1/6/2010)[/b][hr]I chose C because there wasn't a 'D - None of the above'. My instance of SQL 2005 coughs with the error:Msg 208, Level 16, State 1, Line 1Invalid object name 'dbo.Employees'.and returns NULL as a result.[/quote]Try it after you change the table name in the variable to a table that exists in the database you are running the code.

RE: Dynamic SQL

john.curran.z3g — Wed, 06 Jan 2010 06:00:42 GMT

I chose C because there wasn't a 'D - None of the above'. My instance of SQL 2005 coughs with the error:Msg 208, Level 16, State 1, Line 1Invalid object name 'dbo.Employees'.and returns NULL as a result.

RE: Dynamic SQL

BudaCli — Wed, 06 Jan 2010 05:02:19 GMT

Beaten but i learned 1 or 2 things

RE: Dynamic SQL

SRatnaparkhi — Wed, 06 Jan 2010 00:17:55 GMT

I changed the table name, Now it works fine in 2005. good Question! Thanks to Sudhir.

RE: Dynamic SQL

SQLRNNR — Tue, 05 Jan 2010 10:36:29 GMT

[quote][b]rustman (1/5/2010)[/b][hr][quote][b]CirquedeSQLeil (1/5/2010)[/b][hr][quote][b]rustman (1/5/2010)[/b][hr]My SQL2008 system seems to have trouble with the 'quotename' which encloses the tablename in []. Remove that with the dbo in front of the table name and Option C works fine.[/quote]Very interesting. I ran it on my SQL 2k8 database with absolutely no problem.[/quote]I found my problem with the quotename. I used the AdventureWorks Database and pointed it to the Employee Table. The Employee table has a schema of Humanresources. When the quotename encloses the entire table [Humanresources.Employee] this throws the error. The correct syntax for that being [Humanresources].[Employee]. I changed to a different db and chose a table with dbo schema and thus it works fine on SQL2008.[/quote]That makes sense. That might be a good suggestion for updating this question with - alter it to work with different schemas.

RE: Dynamic SQL

rustman — Tue, 05 Jan 2010 10:30:50 GMT

[quote][b]CirquedeSQLeil (1/5/2010)[/b][hr][quote][b]rustman (1/5/2010)[/b][hr]My SQL2008 system seems to have trouble with the 'quotename' which encloses the tablename in []. Remove that with the dbo in front of the table name and Option C works fine.[/quote]Very interesting. I ran it on my SQL 2k8 database with absolutely no problem.[/quote]I found my problem with the quotename. I used the AdventureWorks Database and pointed it to the Employee Table. The Employee table has a schema of Humanresources. When the quotename encloses the entire table [Humanresources.Employee] this throws the error. The correct syntax for that being [Humanresources].[Employee]. I changed to a different db and chose a table with dbo schema and thus it works fine on SQL2008.

RE: Dynamic SQL

SQLRNNR — Tue, 05 Jan 2010 10:12:42 GMT

[quote][b]ziangij (1/4/2010)[/b][hr]option C doesn't work, it throws an error while executing in sql 2005. [/quote]You will need to change the Employees table in the variable to a valid table in your database.

RE: Dynamic SQL

SQLRNNR — Tue, 05 Jan 2010 10:11:58 GMT

[quote][b]rustman (1/5/2010)[/b][hr]My SQL2008 system seems to have trouble with the 'quotename' which encloses the tablename in []. Remove that with the dbo in front of the table name and Option C works fine.[/quote]Very interesting. I ran it on my SQL 2k8 database with absolutely no problem.

RE: Dynamic SQL

SQLRNNR — Tue, 05 Jan 2010 10:10:51 GMT

I agree with Lynn,Mark and Bitbucket. The idea is to test the code against your system using your knowledge. If that means changing a variable - then change it. Not every database will have an Employees table - that was not the actual question. The question was to test how to get appropriate results from one of the Dynamic SQL statements.

RE: Dynamic SQL

Oleg Netchaev — Tue, 05 Jan 2010 08:55:04 GMT

[quote][b]bitbucket-25253 (1/5/2010)[/b][hr]Why kaspencer, why -- so a user can cut and paste ... that is a heck of a way for a person to test their knowledge ... Now if all you want is a method to inflate your ego ... then your suggestion would be appropriate ... but to test your knowledge inappropriate Sudhir Chawal [b]A darn good question[/b][/quote]Exactly!. The whole point of the QoD is to test one's own knowledge, and the best way to do it is to try figuring the correct answer before pasting anything into the editor. Once the answer is submitted then it is OK to play with the sample statements and adjust them if necessary depending on one's settings. Today's question is a very good one indeed. It shows that sp_executesql is far more powerful than simple exec as it allows designing reusable dynamic SQL queries with multiple parameters in either direction (in or out) and possibly useful plan storage. It should always be used with caution as dynamic SQL can be subjected to injection attacks, but other than that the sp_executesql is a great feature.Oleg

RE: Dynamic SQL

bitbucket-25253 — Tue, 05 Jan 2010 08:23:21 GMT

[quote]kaspencer I suggest that before allowing a question to be used, the submitter must ensure that all assumptions and pre-conditions for the question be stated clearly. These assumptions would include such items as database names, table existence, as well as any security or user-privilege features necessary to ensure that the correct response would be produced when the query or procedure is run.I have noted many instances of reports in this forum when queries have failed (and have had some such myself), and I feel that this shows poor appreciation by the submitter that not every system resembles his own.[/quote]Why kaspencer, why -- so a user can cut and paste ... that is a heck of a way for a person to test their knowledge ... Now if all you want is a method to inflate your ego ... then your suggestion would be appropriate ... but to test your knowledge inappropriate Sudhir Chawal [b]A darn good question[/b]

RE: Dynamic SQL

mtassin — Tue, 05 Jan 2010 07:50:48 GMT

Sorry,I have to agree with Lynn.If you don't have a table named employees, then of course it won't work.Copying and pasting the query code into SSMS and running it is the cheap way out.Knowing you don't have a table named employees in whatever scratch database you have, substituting in the table name in the beginning of the question with one you do have in your test database would let you test it properly.set @tablevariable = 'Employees'Notice the beginning of the question.Sam has to run a query dynamically & get the count in a variable and do some processing based on the count. Which of the following queries will return expected output?If you just wanted to run the query, you'd need to substitute in a table in place of the employees one that Sam apparently has.

RE: Dynamic SQL

rustman — Tue, 05 Jan 2010 07:46:30 GMT

My SQL2008 system seems to have trouble with the 'quotename' which encloses the tablename in []. Remove that with the dbo in front of the table name and Option C works fine.

RE: Dynamic SQL

Steve Eckhart — Tue, 05 Jan 2010 07:38:33 GMT

Like others, I answered A because it returned "the expected output": NULL. Option C returns an error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.Employees'.I didn't put in the name of a valid table name in my database because that was not a part of the question. The question had the following as a "prequel" to all three of the queries: declare @tablevariable varchar(100) set @tablevariable = 'Employees'So, for option C, I executed declare @tablevariable varchar(100) set @tablevariable = 'Employees' DECLARE @sql nvarchar(4000), @params nvarchar(4000), @count int SELECT @sql = N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tablevariable) SELECT @params = N'@cnt int OUTPUT' EXEC sp_executesql @sql, @params, @cnt = @count OUTPUT select @count Therefore, option A returns the expected result (since the table variable is empty) and should be a correct answer.

RE: Dynamic SQL

Lynn Pettis — Tue, 05 Jan 2010 06:34:20 GMT

[quote][b]kaspencer (1/5/2010)[/b][hr]I couldn't see the answer to this question, and so I guessed "C" which happened to be correct.However, when I tried the three options on my system, "C" returned an error, "B" also returned an error, and "A" returned NULL.I suspect that my errors had a similar cause to that reported by others. This leads me to make a suggestion for consideration by the adjudicators (if there are any!):[b][i]I suggest that before allowing a question to be used, the submitter must ensure that all assumptions and pre-conditions for the question be stated clearly. These assumptions would include such items as database names, table existence, as well as any security or user-privilege features necessary to ensure that the correct response would be produced when the query or procedure is run.I have noted many instances of reports in this forum when queries have failed (and have had some such myself), and I feel that this shows poor appreciation by the submitter that not every system resembles his own.[/i][/b]Happy New Year to one and all!Ken.[/quote]I disagree. If you want to run the code in your environment, you should take the appropriate steps based on the code to ensure that it runs. I ran the code in a sandbox database where I had to make appropriate changes, including commenting code as I ran each option to see what occurred.The reason is the same as you mentioned, the person providing a QotD can't know how everyone's environment is setup. Do you have a AdventureWorks installed on your servers at work? I don't. Do you have your databases on your D: drive, or your backup files on the E: drive? There are so many possibilities that it isn't possible for a person to ensure that the code will work on everyones instance of SQL Server.After answering the question, I tested the code.First option, returned null.Second option, an error.Third option, correct result based on the table I used in the original modification for the table to query.All of that confirmed my original answer of C.

RE: Dynamic SQL

Dietmar Weickert — Tue, 05 Jan 2010 03:10:15 GMT

Cool question! Taught me about a new way to execute dynamic SQL.Thank you Sudhir.To all who reported troubles: It works perfectly on my instance of SS2005. What messages did you receive?

RE: Dynamic SQL

kaspencer — Tue, 05 Jan 2010 02:50:51 GMT

I couldn't see the answer to this question, and so I guessed "C" which happened to be correct.However, when I tried the three options on my system, "C" returned an error, "B" also returned an error, and "A" returned NULL.I suspect that my errors had a similar cause to that reported by others. This leads me to make a suggestion for consideration by the adjudicators (if there are any!):[b][i]I suggest that before allowing a question to be used, the submitter must ensure that all assumptions and pre-conditions for the question be stated clearly. These assumptions would include such items as database names, table existence, as well as any security or user-privilege features necessary to ensure that the correct response would be produced when the query or procedure is run.I have noted many instances of reports in this forum when queries have failed (and have had some such myself), and I feel that this shows poor appreciation by the submitter that not every system resembles his own.[/i][/b]Happy New Year to one and all!Ken.

RE: Dynamic SQL

Rupal Raval — Tue, 05 Jan 2010 00:20:44 GMT

what error does it throw?have you written 'declare @tablevariable varchar(100)set @tablevariable = 'any valid table name'Before code C?map @tablevariable to one of your database table, select that database as current database and then execute the code

RE: Dynamic SQL

Ashka Modi — Tue, 05 Jan 2010 00:17:28 GMT

It works fine in 2005. good Question!

RE: Dynamic SQL

ziangij — Mon, 04 Jan 2010 22:42:19 GMT

option C doesn't work, it throws an error while executing in sql 2005.

RE: Dynamic SQL

SQLRNNR — Mon, 04 Jan 2010 22:33:40 GMT

Thanks for the question.

Dynamic SQL

Sudhir-192949 — Mon, 04 Jan 2010 21:47:52 GMT

Comments posted to this topic are about the item [B]Dynamic SQL[/B]