Tuesday, March 20, 2012

Can this be Done?

Hi, just wondering if something like this can be done

procedure [dbo].[UpdateStringColumn]
@.KeyValue int,
@.Column varchar(50),
@.value float,
@.TableName varchar(30),
@.KeyName varchar(30)
as
begin
exec('UPDATE '+@.TableName+'
Set '+@.column+ ' = '+@.value+'
where '+@.TableName+'.'+@.KeyName+' = '+@.KeyValue+'' )
end

If I can get this to work I only need a few stored procedures to update any table column combination, one for each @.Value datatype

Well, it can be done. But it is a bad idea. Using dynamic SQL like this has lot of implications - maintainability, security risks, performance problems, manageability etc. See below link for some discussion:

http://www.sommarskog.se/dynamic_sql.html

Other things to ponder: Why do you want to have single SP to update data? And what happens if you want to update multiple columns in a table? What happens if the data type of each column in different? What happens if there is some function in the client that need to only update one column and another function that updates entire row? What happens if you need to perform additional validations for updates on some tables? What happens if there are some users who can only update some tables? How will you manage the permissions since with dynamic SQL you need to grant all the required permissions to users directly?

No comments:

Post a Comment