July 20, 2008 at 9:37 am
I have an update procedure for my Location table. Out of all of these fields I'm updating, the only required field in the table is the LocName.
The problem that I'm running into is if, for example, I'm only updating the the Phone, DateMod, and ModBy fields, it says that LocName is a required field.
I'm still new to all of this, but can anyone give me some advice or help me figure out what's going on?
Thanks!!!
ALTER PROCEDURE [dbo].[LOC_EditLoc]
(
@LocationID bigint,
@LocName nvarchar(50),
@LocAddress nvarchar(50),
@LocCity nvarchar(50),
@LocState char(2),
@LocZip char(5),
@LocPhone char(15),
@LocDateMod smalldatetime,
@LocModBy nvarchar(50)
)
AS
SET @LocDateMod = GetDate()
UPDATE Location
SET
LocName = @LocName, LocAddress = @LocAddress, LocCity = @LocCity, LocState = @LocState,
LocZip = @LocZip, LocPhone = @LocPhone, LocDateMod = @LocDateMod, LocModBy = @LocModBy
WHERE LocationID = @LocationID
July 20, 2008 at 10:49 am
jbloes (7/20/2008)
I have an update procedure for my Location table. Out of all of these fields I'm updating, the only required field in the table is the LocName.The problem that I'm running into is if, for example, I'm only updating the the Phone, DateMod, and ModBy fields, it says that LocName is a required field.
I'm still new to all of this, but can anyone give me some advice or help me figure out what's going on?
Thanks!!!
ALTER PROCEDURE [dbo].[LOC_EditLoc]
(
@LocationID bigint,
@LocName nvarchar(50),
@LocAddress nvarchar(50),
@LocCity nvarchar(50),
@LocState char(2),
@LocZip char(5),
@LocPhone char(15),
@LocDateMod smalldatetime,
@LocModBy nvarchar(50)
)
AS
SET @LocDateMod = GetDate()
UPDATE Location
SET
LocName = @LocName, LocAddress = @LocAddress, LocCity = @LocCity, LocState = @LocState,
LocZip = @LocZip, LocPhone = @LocPhone, LocDateMod = @LocDateMod, LocModBy = @LocModBy
WHERE LocationID = @LocationID
The way you have written the procedure requires all parameters to the procedure to be passed in. You either need to pass in all parameters to the stored procedure, or modify the procedure and code to accept default values.
One way to do this is:
ALTER PROCEDURE [dbo].[LOC_EditLoc]
(
@LocationID bigint,
@LocName nvarchar(50) = NULL,
@LocAddress nvarchar(50) = NULL,
@LocCity nvarchar(50) = NULL,
@LocState char(2) = NULL,
@LocZip char(5) = NULL,
@LocPhone char(15) = NULL,
--@LocDateMod smalldatetime = NULL,
@LocModBy nvarchar(50) = NULL
)
AS
UPDATE Location
SET LocName = COALESCE(@LocName, LocName)
,LocAddress = COALESCE(@LocAddress, LocAddress)
,LocCity = COALESCE(@LocCity, LocCity)
,LocState = COALESCE(@LocState, LocState)
,LocZip = COALESCE(@LocZip, LocZip)
,LocPhone = COALESCE(@LocPhone, LocPhone)
,LocDateMod = getdate()
,LocModBy = COALESCE(@LocModBy, LocModBy)
WHERE LocationID = @LocationID;
Notice the COALESCE statements. If the parameter is NULL, the COALESCE will use the column value instead of the parameter. I also removed @LocDateMod as a parameter - since you really want that to be updated to the current date/time for all updates.
The other option is to require all parameters and pass all parameter values to the procedure. Most of the time, this makes the most sense since then your client software does not have to build the statement to only update the columns that have changed.
The client software should always send all columns in the procedure - that way it is much easier to code and you don't have to worry about identifying which columns the user has changed. You just take all values and pass them to the procedure.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2008 at 11:01 am
Jeffrey is correct, the best way to do it is to pass all parameters to the sp from the application. It makes things easier on the programmer and does not harm anything.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 20, 2008 at 11:49 am
Ok, sounds good.
Chances are I will use Visual Studio's GridView when using that update procedure.
I believe that will always pass in the parameters that I have listed.
So in knowing that - would I still want to use the coalesce or would what I have be not perfecdt, but sufficient?
I'm just reading whatever books I can find, but alot of times they don't have a lot of real life examples - so I like to hear the good practices and real solutions.
Thanks!!!
-Jeff
July 20, 2008 at 12:07 pm
If you are going to be passing in all of the parameters, then no - you don't need to coalesce the parameters.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply