Generic update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generic update

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-12-15 : 06:12:38

Hi,
I would like to have one update query that takes care of the fact whether there is a DepartmentName value or not.
For example, can you edit this stored procedure so that even if the DepartmentName is NOT passed in then it does not update it's value and will therefore leave it's value as it exists.
At present if the DepartmentName is not passed iin, it gets updated to null. I can place an if statement and have another update query but I would like to have only one update that handles both cases.

Thanks

create stored procedure uspUpdate

@DepartmentID = int
@DepartmentName varchar(50) = null
@FirstName varchar(50)
@LastName varchar(50)

AS

update
tblDepartment
set
FirstName = @FirstName,
LastName = @LastName,
DepartmentName = @DepartmentName
where
DepartmentID = @DepartmentID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 06:20:47

Alter stored procedure uspUpdate

@DepartmentID = int
@DepartmentName varchar(50) = null
@FirstName varchar(50)
@LastName varchar(50)

AS

update
tblDepartment
set
FirstName = @FirstName,
LastName = @LastName,
DepartmentName = ISNULL(@DepartmentName,DepartmentName)
where
DepartmentID = @DepartmentID

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-12-15 : 06:32:39

Trouble is that you cannot then change a column from Something to Null.

We pass all our parameters to our Generic Save Sprocs as VARCHAR and then if they are:

NULL (the default) - leave existing column value as-is
'' (empty string) - update the column to NULL
other value - update the column to the new value

This allows us to have "Save" Sprocs that have no effect on columns [parameters] which are not provided.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 06:40:19

<<
Trouble is that you cannot then change a column from Something to Null.
>>

+100

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-12-16 : 04:42:01

"points-padding" Madhi?? !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-16 : 06:54:54