CODEWITHSUNDEEP
sqlstored-proceduresnulloptional-parameters
rpf3
rpf3

Reputation: 691

Stored Procedure NULL Parameter

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.

Upvotes: 6

Views: 56600

Answers (3)

John
John

Reputation: 107

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;

Upvotes: 3

HLGEM
HLGEM

Reputation: 96572

Take out the default value and and then the code calling the proc must provide a value (either a real value or NULL)

Upvotes: 0

Tony Casale
Tony Casale

Reputation: 1537

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.

Upvotes: 7

Related Questions