June 19, 2012 at 6:31 pm
SSCrazy Eights - if reading this, On a similar CRUD note, it may handle it when I download the tools for SSMS you told me about-super)
*Note Front End is MVC4 using Entity Framework 4.X (CRUD) scaffolding for those of you that are also familiar with it.
However I need to learn how to handle an Update SPROC when some table columns are not nullable...
Will I need to execute the Update sproc separately in the front end and preceed it with
code like below to pass to the (Not Null parms) columns that the user did not chose to update?
example:
Declare @Interview_Number int
IF @interview_Number IS NULL
select @interview_Number = interview_Number from Interviews where ID = @ID
EXECUTE usp_UPDATE_Interviews
@ID = @ID,
@Interview_Number = @Interview_Number,
@parmChosenToBeUpdated = @parmChosenToBeUpdated ,...
Here's the table DDL. It is very important I do this the correct way from the get go so I thank you all very much for your help. Here is my Create Table with unecessary to the question columns removed:
create TABLE [dbo].[Interviews](
[ID] [int] IDENTITY(1,1) NOT NULL ,
[Date] [date] NOT NULL ,
[Interview_Number] [int] NOT NULL ,
[Time] [nvarchar](50) NULL ,
[Place] [nvarchar](50) NULL
) ON [PRIMARY]
GO
And here is my unsuccessful attempt to handling 'nulls' going into non nullable columns on this update sproc:
CREATE PROCEDURE usp_UPDATE_Interviews
@ID int , --filter
@Date date = NULL, --Null here does nothing, table column not null property overrides
@Interview_Number int = NULL , --Null here does nothing, table overrides
@Time varchar(50) = NULL ,
@Place varchar(50) = NULL
AS
BEGIN
IF @Date IS NULL
select @date = [DATE] from Interviews where ID = @ID
IF @interview_Number IS NULL
select @interview_Number = interview_Number from Interviews where ID = @ID
UPDATE Interviews
SET
[Date]=@Date,
[Interview_Number] = @Interview_Number,
[Time] = @Time,
[Place] = @Place
WHERE ID= @ID
END
GO
Since handling the nulls in the sproc does not seem to work, how should I be handling them?
Thanks!!
June 19, 2012 at 11:34 pm
If am interpreting your question correctly you want to be able to leave parameters to the stored proc null, and have the procedure update the correct row(s) leaving the columns whos parameters are null with their old values.
If so you can remove the slect statements you were using to retrieve the current values of those columns and change the update to something like:
UPDATE Interviews
SET
[Date]=coalesce(@Date,[Date]),
[Interview_Number] = coalesce(@Interview_Number,coalesce[Interview_Number])
.... etc
Mike
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply