A Microsot Bug in Dynamic Comlumns?

  • Thread starter Thread starter valentin tihomirov
  • Start date Start date
valentin said:
It is advised to enforce security (mod access to tables is forbidden)
in addition to the speed.
Click to expand...

In my opinion, using a stored procedure which executes dynamic SQL will
not be any better than executing the sp_executesql procedure directly,
in both security and speed. You still need to allow the user direct
access to the tables (unless you sign your procedure with a
certificate) and there will be parametrised execution plans cached for
sp_executesql anyway.

I'd appreciate if Erland and the other MVP-s could post their opinion,
too (supporting or opposing my opinion, with better arguments).
 
Razvan said:
In my opinion, using a stored procedure which executes dynamic SQL will
not be any better than executing the sp_executesql procedure directly,
in both security and speed. You still need to allow the user direct
access to the tables (unless you sign your procedure with a
certificate) and there will be parametrised execution plans cached for
sp_executesql anyway.

I'd appreciate if Erland and the other MVP-s could post their opinion,
too (supporting or opposing my opinion, with better arguments).
Click to expand...

There is really not much add. Generally, I tend to think that for a stored
procedure with dynamic SQL to be worthwhile there need to be some level of
complexity that is worth packaging. Code like

'UPDATE tbl SET ' + quotename(@colname) + ' = @value ' +
WHERE somekey = @key'

does not really meet that level. And it looks clunky, because you will need
different procedure for different data types. Or use sql_variant.

In practice, a GUI usually goes with a procedure that updates most columns
in the table (those exposed in the GUI). If there are other function where
only a single column needs to be updated, you would typically have a
separate procedure for that.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
Erland said:
In practice, a GUI usually goes with a procedure that updates most
columns in the table (those exposed in the GUI). If there are other
function where only a single column needs to be updated, you would
typically have a separate procedure for that.
Click to expand...

And if the GUI is smart enough to know which columns have been changed
by the user (and wants to update only those columns), then it should
build a dynamic UPDATE statement on the client side and send it to
sp_executesql, along with parameters for the values of those columns.
 
Razvan said:
And if the GUI is smart enough to know which columns have been changed
by the user (and wants to update only those columns), then it should
build a dynamic UPDATE statement on the client side and send it to
sp_executesql, along with parameters for the values of those columns.
Click to expand...

And this really brings us to the point, if the GUI has all the value of
all those columns, then how much do you really gain by just updating
the columns that have actually changed? You save some network bandwidth,
but that's about it. OK, if there is a trigger on the table with
IF UPDATED in it, there may be more to win. Still it's not obvious
that this warrants the increased gain in complexity.

Having a stored procedure which handles all the columns owned by this
particular GUI appears to be a simple way to go.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
Back
Top