The Daily WTF: Curious Perversions in Information Technology

2006-02-06 Reply Admin

Anonymous:
Quinnum

Actually one of the last projects I did, was to take some very very unreadable dynamic SQL Server SQL code and structure it using some pretty standard logic. Yes the procedure was roughly 20 times longer when I had finished, however it was also infinitely more readable and roughly five times as quick.

In terms of coding, 99% of the time Dynamic SQL is the quick fix (as you have stated), however in terms of efficiency Dynamic SQL is the wrong choice if the logic can be coded in a more conventional manner, try it yourself, you may be surprised!!!

I am not a purist by nature, however saying that I look for the most efficient way for something to work, are you saying that you dont?

The great thing about anecdotal evidence is that it is so convincing!

2006-02-09 Reply Admin

Its not all about performance.

The main difference is in permissions.
If you use dynamic SQL the user will require select permisssion on the tables as opposed to controlling just exec perm on the SP.
Some of our clients only allow SP execution for applications so dynamic SQL is not an option without a strong business case.

In this case if you allow dynamic SQL all you will get is the same messy IFs out in the application anyway.

ammoQ · 2006-02-09 Reply Admin

Anonymous:
Its not all about performance.

The main difference is in permissions.
If you use dynamic SQL the user will require select permisssion on the tables as opposed to controlling just exec perm on the SP.
Some of our clients only allow SP execution for applications so dynamic SQL is not an option without a strong business case.

In this case if you allow dynamic SQL all you will get is the same messy IFs out in the application anyway.

In Oracle, it's the owner of a stored procedure who needs the right to access a table; the user who calls that procedure doesn't need any privileges on that table, just the right to execute the procedure. Dynamic SQL doesn't behave differently in that respect, but you need to be carefull anyway, because if the procedure doesn't care about SQL injection attacks, other users (who have the right to call the procedure) might be able to do evil things within the security context of the owner of the procedure.

Let's Go Dynamic

Leave a comment on “Let's Go Dynamic”