July 13, 2010 at 8:52 am
One way to write an Update store procedure that can handle one or all columns in the table is this:
In your stored procedure, declare a parameter for every column in the table and default them to NULL (e.g. @ColumnA nvarchar(50) = NULL).
In your SET clause, add a line for each column that looks like this:
ColumnA = COALESCE(@ColumnAParam, ColumnA).
In this way, if the paramter has a value then that column will be updated; if not, the column will retain its original value.
When you call the SP, only pass in the parameters that you want to update.
If you use a data access layer built around, say a .NET class, you can design it so that it only passes in parameters for members whose value has been set.
The biggest flaw in this design is that you can never use the SP to set a column that currently has a value back to NULL (you can set a number to 0, or a varchar to '', but not back to NULL). You can get around this by trying to come up with a value that would never occur in your data set naturally and write kludges that would make that value = NULL, but this isn't always necessary.
Here's an example for a three column (plus PK) table:
CREATE PROCEDURE [dbo].[UpdateTable1]
(
@PrimaryKey_idint,
@ColumnA_txnvarchar(50) = NULL,
@ColumnB_txnvarchar(5) = NULL
@ColumnC_nbint) = NULL
)
AS
BEGIN
UPDATE
dbo.Table1
SET
ColumnA_tx = COALESCE(@ColumnA_tx, ColumnA_tx),
ColumnB_tx = COALESCE(@ColumnB_tx, ColumnB_tx),
ColumnC_nb = COALESCE(@ColumnC_nb, ColumnC_nb)
WHERE
PrimaryKey_id = @PrimaryKey_id
END
So if you call this in this way:
EXEC UpdateTable1
@ColumnA_tx = 'abc'
Then ColumnA_tx will receive the new value 'abc', but the other columns will retain their original values.
Hope this helps.
July 14, 2010 at 1:09 am
Another option is to use dynamic SQL in your stored procedure:
--exec UpdateContact 30, 'Joe','Blow',NULL
alter PROCEDURE UpdateContact
@ContactID int
,@FirstName nvarchar(30)
,@LastName nvarchar(30)
,@Age int
AS
BEGIN
declare @SQL nvarchar(4000)
declare @StartLen int
SET NOCOUNT ON;
set @SQL = 'Update Contact set '
set @StartLen = len(@SQL)
if(@FirstName is not null)
begin
set @SQL = @SQL + ' FirstName = ''' + @FirstName + ''''
end
if(@LastName is not null)
begin
if(len(@SQL) > @StartLen)
set @SQL = @SQL + ' ,LastName = ''' + @LastName + ''''
else
set @SQL = @SQL + ' LastName = ''' + @LastName + ''''
end
if(@Age is not null)
begin
if(len(@SQL) > @StartLen)
set @SQL = @SQL + ' ,Age = ' + cast(@Age as varchar(20))
else
set @SQL = @SQL + ' Age = ' + cast(@Age as varchar(20))
end
set @SQL = @SQL + ' Where ContactID = ' + cast(@ContactID as varchar(20))
exec(@SQL)
END
GO
July 14, 2010 at 7:59 am
If you follow Paul's suggestion to do dynamic SQL, you should take care to avoid SQL Injection attacks. If you don't protect against this then a line of code like the following can be potentially dangerous:
if(@FirstName is not null)
begin
set @SQL = @SQL + ' FirstName = ''' + @FirstName + ''''
end
What if someone uses your web form to set @FirstName = '; DELETE FROM MainTable1' or '; SELECT * FROM UserTable'? Potentially, you could lose data or have confidential data exposed.
You could try to trap for this sort of stuff as part of form validation or in your DAL, but you can also do it in the back-end.
Best practice is to keep the parameters encapsulated in your dynamic sql (i.e. do not convert them to their values during the building of the SQL) and use the sp_executesql command, which accepts, besides the SQL command itself, a defined parameter signature and the parameter variables themselves. Using this construct, the query engine will not allow potentially destructive SQL commands to be passed in via parameters.
So, if you change Paul's code to the following, you should be protected from SQL injection:
ALTER PROCEDURE UpdateContact
@ContactID int,
@FirstName nvarchar(30) = NULL,
@LastName nvarchar(30) = NULL,
@Age int = NULL
AS
BEGIN
declare @SQL nvarchar(4000)
declare @StartLen int
declare @paramList_tx nvarchar(255)
SET NOCOUNT ON;
set @SQL = 'Update Contact set '
set @StartLen = len(@SQL)
if(@FirstName is not null)
begin
set @SQL = @SQL + ' FirstName = @FirstName '
end
if(@LastName is not null)
begin
if(len(@SQL) > @StartLen)
set @SQL = @SQL + ' ,LastName = @LastName '
else
set @SQL = @SQL + ' LastName = @LastName '
end
if(@Age is not null)
begin
if(len(@SQL) > @StartLen)
set @SQL = @SQL + ' ,Age = @Age'
else
set @SQL = @SQL + ' Age = @Age'
end
set @SQL = @SQL + ' Where ContactID = @ContactID'
set @ParamList_tx =
'
@ContactID int,
@FirstName nvarchar(30),
@LastName nvarchar(30) ,
@Age int
'
exec sp_executesql
@strSQL,
@paramList,
@ContactID int,
@FirstName nvarchar(30),
@LastName nvarchar(30),
@Age int
END
July 14, 2010 at 9:38 am
If you use the .Net SQLParamater, it will not allow that value
July 14, 2010 at 11:55 am
If you use the .Net SQLParamater, it will not allow that value
Hi, Paul,
I'm not clear on what value you're referring to above, but even using a SQLParameter object (I've only tested in .Net 2, but I think this is true for 3.5 as well), you can pass Select and Delete statements through and these could be executed using exec(@Sql). After all, there may be legitimate uses of this functionality.
Obviously, in a well designed application, you would deny un-needed permissions (such as Delete) at, at least, the table level to the account you're using for your database connections. This would add an extra layer of security.
But I don't believe that using .NET SQLParameters alone offers protection against injection so you should be careful if you have such code in your production environment.
Hope this helps.
July 14, 2010 at 12:03 pm
paul.wetzel (7/14/2010)
If you use the .Net SQLParamater, it will not allow that value
Why not?
Just parameterising the call to the proc is not sufficient to protect against injection if there's dynamic SQL that concatenated input into a string and executes it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2010 at 5:04 pm
I tested i and it doed get through, so I stand corrected. I have not been able to get anything to execure thus far though
July 15, 2010 at 7:32 am
@OP
How are you getting the original values of the data to display in the form? If it is coming from SQL Server, then why not put the data into a dataset? Write the SP to update all columns, and then create the dataset using the Dataset Designer in Visual Studio. Use pessimistic concurrency, and point the update statement to the SP you created. .Net will then automatically create the parameter mappings to correctly pass values to the stored procedure regardless of which columns have been modified. Then, you can just call the DataAdapter.Update method, passing in the row(s) that have been modified.
Also, to make your coding much easier, bind the textboxes (or other controls) to the datacolumns that they map to in the dataset.
Good luck, and also, Thank you for your service.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply