A Microsot Bug in Dynamic Comlumns?

[Razvan Socol]

It is advised to enforce security (mod access to tables is forbidden)
in addition to the speed.

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

 

[Erland Sommarskog]

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

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

 

[Razvan Socol]

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.

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.

 

[Erland Sommarskog]

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.

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