Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi-statement execution


Multi-statement execution

Author
Message
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.

SQL DBA.
Longy
Longy
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 944
SanjayAttray (2/3/2010)
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.



What would have running it in SSMS and putting the answer in have achieved apart from getting you the points?



Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
This is an excellent question, I thoroughly enjoyed it. I guess the reason why initially it is tempting to select 0 as a correct answer is incorrect assumption about when does the DSql gets injected. In other words, if the insert into portion was a part of the DSql itself then the answer would be a no-brainer (0). To figure that the execution works as it is explained is not easy, but of course makes perfect sense. There is an insert into followed by some statement to parse, and the engine expects the latter to possibly have something selected from somewhere. The select portion of the DSql does it, the delete does not change the originally selected data as it is already on the heap, and so the insert of 4 rows takes place.

I wish we had more questions like this! Thank you!

Oleg
gchornenkyy
gchornenkyy
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 374
Thank you for all your comments.
Unfortunately no explanation links to MSDN or BOL ... probably because of that's a simplified real life example :-)
Actually to believe in this kind of "functionality" I had to develop my own "clean" code that was published later as QoD.

Thanks,
Gennadiy
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
I hadn't considered the SQL Injection aspect of this question. Thanks for pointing that out.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
It is interesting how the engine behaves when the second part of DSql changes to something different. For example, if instead of deleting the records from the table the second part of the DSql tries to say, drop it then the whole insert actually fails with error stating that insert failed because the statement tried to change the table schema. If the delete from #funny_test is replaced with select 1 then insert fails because it actually tries to accumulate all select(s) results on the hip and the select 1 portion does not have the value for the second column. But if delete from #funny_test is replaced with for example, select 99, 99 then insert succeeds and the total number of the records in the tables happens to be 15 (!), i.e. 10 original records, 4 inserted because of the first select of the DSql, and 1 inserted from the second select of DSql. In this case the ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) +'; select 99, 99;' is functionally equivalent to ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) + ' union all select 99, 99;'

Oleg
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1803
I thoroughly enjoyed this question. After the raft of recent questions with debatable answers, this had no 'trick' and tested several concepts, ie:

1) Temp tables can be referenced by "lower level" code, whether that be an EXEC() or a stored proc
2) The lower level code will execute in it's entirety before it returns to the calling code
3) Results are stored in places outside of the referenced tables (ie: memory or tempdb, obviously)
4) Illustrates an example of SQL Injection


S.
bdba
bdba
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 338
Good question. I guessed 0 thinking it was SQL injection issue which would delete all the rows in the table.Learnt sth new.
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2413 Visits: 8062
Interesting question, thanks Smile

I got it right, but largely down to guesswork - my initial thought was 0, but the name of the temp table "#funny_test" made me expected something, well, funny - so I thought again!
Bhavesh_Patel
Bhavesh_Patel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 297
Good One ...



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
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