December 15, 2009 at 8:07 pm
Hello, We use stored procedures to update tables. The stored procedure takes all updatable columns as input parameters and uses the following method to update columns (e.g., @KeyID, @Col1, @Col2, @Col3).
UPDATE tbl
SET Column1 = ISNULL(@Col1, tbl.Column1)
, Column2 = ISNULL(@Col2, tbl.Column2)
, Column3 = ISNULL(@Col3, tbl.Column3)
FROM dbo.Table1 tbl
WHERE tbl.ID = @KeyID
If Column2 has some value and if it needs to be set to NULL, is there any best practice that I can follow?
December 15, 2009 at 9:04 pm
prasad.perini (12/15/2009)
If Column2 has some value and if it needs to be set to NULL, is there any best practice that I can follow?
Hi,
Even column having the value but the passed parameter is null, then the column updates the null.
In case one, the update column is null and the passed parameter also null, (Need the result is column become null). Case two, the update column is null and the passed parameter having some value, (Need the result is column become parameter value). Case three, the update column having some value and the passed parameter is null, (Need the result is column become null). Case four, the update column having some value and the passed parameter having some value. (Need the result is column become parameter value)
For the all cases you just pass the parameter as it is like
UPDATE tbl
SET Column1 = @Col1
, Column2 = @Col2
, Column3 = @Col3
FROM dbo.Table1 tbl
WHERE tbl.ID = @KeyID
December 15, 2009 at 9:12 pm
Thanks, for the reply. My problem is that the table has many columns, and in each call, the middle tier calls the stored procedure with only changed values. If the middle tier has to pass all the columns in every call, the performance will suffer. Many tables haver over 30 columns and some have over 100 columns. Is there any suggestion besides passing all columns in very call?
Thanks.
December 15, 2009 at 9:19 pm
Hi,
There little confusion, actually you have to update the column are null in the table is in it, or to update the column by the passing parameters are having the value?
December 15, 2009 at 10:13 pm
The update stored procedure is created with parameters to receive values for all updatable values, with default value of parameters set to NULL (e.g., @Col1 = NULL, @Col2 = NULL, @Col3 = NULL). The stored procedure may be called by passing one or more parameters.
Case1: Initially column1, column2, column3 could be NULL. Later when the stored procedure is called with @Col1 =1, @Col2=2, @Col3=3, columns are set to 1, 2, 3 respectively.
Case 2: The SP may be called by passing only @Col2=5. In this case, the Column2 will be updated with 5 and since @Col1 and @Col3 are NULLs, the corresponding columns are set to their existing values in the table (i.e., 1 and 3 respectively). Column values are Column1=1, Column2=5, Column3=3.
Case3: Now user realizes that the Column2 is set incorrectly, and to start with, it shouldn't have been set with any value at all. It needs to be set to NULL. If NULL is passed, the following UPDATE statement ignores NULL in @Col2 and sets it to its current value, which is 5):
UPDATE tbl
SET Column1 = ISNULL(@Col1, tbl.Column1)
, Column2 = ISNULL(@Col2, tbl.Column2)
, Column3 = ISNULL(@Col3, tbl.Column3)
FROM dbo.Table1 tbl
WHERE tbl.ID = @KeyID
How can I distinguish if NULL is intended to be used to set the column with NULL or if the parameter is not passed and NULL (default value of the column) should not be used to update the table.
I hope it is making sense. Thanks.
December 15, 2009 at 10:18 pm
The update stored procedure is created with parameters to receive values for all updatable values, with default value of parameters set to NULL (e.g., @Col1 = NULL, @Col2 = NULL, @Col3 = NULL). The stored procedure may be called by passing one or more parameters.
Case1: Initially column1, column2, column3 could be NULL. Later when the stored procedure is called with @Col1 =1, @Col2=2, @Col3=3, columns are set to 1, 2, 3 respectively.
Case 2: The SP may be called by passing only @Col2=5. In this case, the Column2 will be updated with 5 and since @Col1 and @Col3 are NULLs, the corresponding columns are set to their existing values in the table (i.e., 1 and 3 respectively). Column values are Column1=1, Column2=5, Column3=3.
Case3: Now user realizes that the Column2 is set incorrectly, and to start with, it shouldn't have been set with any value at all. It needs to be set to NULL. If NULL is passed, the following UPDATE statement ignores NULL in @Col2 and sets it to its current value, which is 5):
UPDATE tbl
SET Column1 = ISNULL(@Col1, tbl.Column1)
, Column2 = ISNULL(@Col2, tbl.Column2)
, Column3 = ISNULL(@Col3, tbl.Column3)
FROM dbo.Table1 tbl
WHERE tbl.ID = @KeyID
How can I distinguish if NULL is intended to be used to set the column with NULL or if the parameter is not passed and NULL (default value of the column) should not be used to update the table.
I hope it is making sense. Thanks.
December 15, 2009 at 10:32 pm
Hi,
This handled by using the dynamical sql updating statement
create table MYTABLE
(
KEYID varchar(5),
col1 varchar(10) null,
col2 varchar(10) null,
col3 varchar(10) null,
col4 varchar(10) null,
col5 varchar(10) null,
col6 varchar(10) null,
)
insert into MYTABLE
select 1,'A','AA','AAA','AAAA','AAAAA','AA'
declare @KEYID varchar(5),@col1 varchar(10),@col2 varchar(10) ,
@col3 varchar(10) ,@col4 varchar(10) ,@col5 varchar(10) ,@col6 varchar(10)
set @KEYID = 1
set @col1 = ''
set @col2 = ''
set @col3 = ''
set @col4 = ''
set @col5 = ''
set @col6 = 'AAAAAA'
declare @columns nvarchar(1000)/*should be max*/
set @columns = (case when @col1 <> '' then 'col1 = '''+@col1+''', ' else ''end)+
(case when @col2 <> '' then 'col2 = '''+@col2+''', ' else ''end)+
(case when @col3 <> '' then 'col3 = '''+@col3+''', ' else ''end)+
(case when @col4 <> '' then 'col4 = '''+@col4+''', ' else ''end)+
(case when @col5 <> '' then 'col5 = '''+@col5+''', ' else ''end)+
(case when @col6 <> '' then 'col6 = '''+@col6+''', ' else '' end)
select @columns = rtrim(@columns)
select @columns = replace(@columns,(right(@columns,1)),'')
select @columns = 'update MYTABLE SET '+@columns+' where KEYID = '+ @KEYID
select @columns
exec sp_executesql @columns
December 17, 2009 at 1:01 pm
I wrote the stored procs for this at hsn for product updates and it was similar except the proc call was based on an xml message. just like u said though. The proc call can me made w/ 1 to n parameters in the call. So one call may have 5 params in it and the same proc will later have a call w/ two params. Really cool stuff i thought. Either way here is how i did it.
create proc testProc
@param1 varchar(10) null
@param2 varchar(10) null
@param3 varchar(10) null
@param4 varchar(10) null
@param5 varchar(10) null
as
update product
set param1 = nullIf( isnull( @param1,param1 ) , '-1')
, param2 = nullIf( isnull( @param2,param2 ) , '-1')
, param3 = nullIf( isnull( @param3,param3 ) , '-1')
, param4 = nullIf( isnull( @param4,param4 ) , '-1')
etc.. So the idea here is there are three scenerios.
1) the param is passed to the proc....everything works as designed
2) the param is not passed then set it to what it already is in the db
3) the param is passed as '-1'. This means the value in the db should be set to null
The key here and this may be a deal breaker is all the processes calling the proc need to be smart enough to pass in a -1 or some other predetermined value when the column value should be nulled out.
December 17, 2009 at 1:11 pm
In the above situation the product changes were coming as xml messages because the changes were actually happening off site and then adjusted on our side. it was really cool though. Either way we used biztalk to consume the xml and build the proc call so it was in biztalk that the logic for the proc call was made and if it should have a given param in it or not or set to -1. This could have easily been done by applying an xslt to the xml as well however it wouldget pretty nasty.
December 17, 2009 at 1:39 pm
oh and by the way. Personally i realize there is always a solution by using dynamic sql. However, you should stay away from it at all costs unless u absolutely have to use it. There will be instances where it will be the only way and if u have to use it well then u have to use it. I would suggest avoiding it at all costs though.
For one dynamic sql is totally unreadable and two i think the optimizer has issues creating the correct optimized execution plan.
December 17, 2009 at 3:32 pm
I think we will use a predetermined value such as -1 or -99 to recognize when to set the column to null. Thanks for the suggestion.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply