Stored Procedure NULL Parameter
Solution 1
No, you can't detect how @Sort became NULL. If your goal is to capture when it is explicitly set versus it being set by the default, I would suggest using a different default value (maybe one that wouldn't normally be used, like -1). Then you can assume that if @Sort is NULL, it was explicitly passed in, but if it is -1, you know it was set by default.
Solution 2
I think this is what your looking for. If one of the parameters is null, it will updatedit with the value in the database. The other option is update one column at a time.
UPDATE Foo
SET
ShortName = ISNULL(@ShortName, ShortName)
, Sort = ISNULL(@Sort, Sort)
WHERE Id = @Id;
rpf3
Updated on October 19, 2020Comments
-
rpf3 over 3 years
I have a database table I am trying to do an UPDATE/INSERT to with a stored procedure. Let's define the table like so:
CREATE TABLE Foo ( Id INT IDENTITY(1, 1), Name VARCHAR(256) NOT NULL, ShortName VARCHAR(32), Sort INT );
I have written a stored procedure similar to the following:
CREATE PROCEDURE Put_Foo ( @Id INT = NULL OUTPUT, @Name VARCHAR(256), @ShortName VARCHAR(32) = NULL, @Sort INT = NULL ) AS BEGIN SET NOCOUNT ON; SELECT @Id = F.Id FROM Foo AS F WHERE F.Name = @Name; IF (@Id IS NOT NULL) BEGIN UPDATE Foo SET ShortName = @ShortName, Sort = @Sort WHERE Id = @Id; END ELSE BEGIN INSERT INTO Foo ( Name, ShortName, Sort ) VALUES ( @Name, @ShortName @Sort ); SET @Id = SCOPE_IDENTITY(); END RETURN; END;
I've greatly simplified the data structures I am dealing with but I hope this serves my point. My question is in regards to how the parameters are processed. Is there a way to determine within the procedure if @Sort was passed in as NULL or set NULL by the default declaration in the parameter list?
EDIT:
The purpose of this is that I don't want NULL parameters to override any columns in the UPDATE statement unless they are explicitly passed in that way.