SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi-statement execution


Multi-statement execution

Author
Message
Simon Liddle
Simon Liddle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 3178
Interesting question - I guessed 0 rows - I thought that the dynamic SQL would execute and the results from the last statement - the delete statement would be inserted. Not really tried anything like this before - going to have a play with executing multiple statements in EXEC() and find out how it affects other things like @@rowcount as well. :-)
Arjun SreeVastsva
Arjun SreeVastsva
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 1658
Really This is not easy for sql newbies .Tricky question
But You Can Learn more knowledge by this
Simon Liddle
Simon Liddle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 3178
Simon Liddle (2/3/2010)
I thought that the dynamic SQL would execute and the results from the last statement - the delete statement would be inserted.


...and that is nonsense the more I think about it! Smile
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3250 Visits: 3200
Ninja's_RGR'us (2/3/2010)
Joy Smith San (2/3/2010)
Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.


So good questions are only the ones you can answer???


I dint mean it. I mean to say, this question is just tricky.
Playing with words and nothing else.
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3250 Visits: 3200
Simon Liddle (2/3/2010)
Joy Smith San (2/3/2010)
Wel, Usualy I answer the question by reading it on the screen itself. I never copy and paste it in query analyzer.Had I copied it in query analyzer and read I would have definetly give right answer. Hence I dint feel it's a good question.


I don't understand how your reliance on reading the code in QA to get it right has any bearing on the question being good or not....Unsure How would reading it in QA (I assume you do mean reading and not executing) have caused you to work out a different answer?


Yes, you are right. I dont execute and just read it.
The difference is that, as you know when you copy it in QA color changes. I would have easily found that delete statement was a string appended to the insert statement.

Anyways, I was just saying my opinion. Hope I have the freedom to post what I feel, instead of simply praising always.

And yes, probably for a beginner it might be a good question. I agree.
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1947 Visits: 1323
Very nice question. Nice SQL inject with a side effect.
I hope I will never use something like this one.



See, understand, learn, try, use efficient
© Dr.Plch
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1241 Visits: 996
An excellent question. The best questions get you thinking in new ways about tools you use all the time, and this certainly fits the bill.

Ultimately it came down to a coin-flip for me (which I consider a fail regardless of the fact that I picked the right answer), but once I read the explanation it made perfect sense. Of course the DSQL is going to execute completely before the insert takes place. Duh!

That said, I would never write code like this, but who knows... some day I might need a magic trick, and this example might point me in the right direction! :-)

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3713 Visits: 5549
nice question sir
Ronald H
Ronald H
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 620
I guess the color coding in QA/SSMS does it? Paste it instead into Notepad++ or an equal text editor that has color coding for SQL, so you can't accidentally execute the code...

Anyone who has to deal with SQL injection can learn from this though, so I think it's a good question.

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Steve Eckhart
Steve Eckhart
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 8664
Thanks for a good question. For some reason, I was thinking that what would be inserted would be the deletion of the table.



Steve Eckhart
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search