CODEWITHSUNDEEP
sqlt-sqlprocedure
akaDka
akaDka

Reputation: 43

how to update a nullable column in sql server stored procedure

I have a stored procedure that takes care of updates to my person table. I don't always update every column so I've provided default values (null) for some of the procedure parameters. But, what if I really want to set the person.middleinitial to null? How does the procedure know my intent?

In reality my person table has LOTS of columns and many are nullable so this can become burdensom depending on the solution.

ALTER PROCEDURE [dbo].[uspPerson_update]     
@id uniqueidentifier,     
@fname nvarchar(20) = NULL,     
@minitial nvarchar(1) = NULL,     
@lname nvarchar(20) = NULL 
AS BEGIN     
SET NOCOUNT ON;     
UPDATE Person      
SET 
fname=ISNULL(@fname,fname),          
minitial=ISNULL(@minitial,minitial),          
lname=ISNULL(@lname, lname)
WHERE id=@id 
END

Upvotes: 3

Views: 2615

Answers (1)

MatBailie
MatBailie

Reputation: 86735

Don't set initials to NULL, set them to a zero length/empty string ''.

Then you can tell the difference between "not specified" (NULL) and "no initial" ('').

Upvotes: 4

Related Questions