Stored procedure with parameter that defaults to default constraint value

Question

I have a Users table that has an ImgPath column. I want that ImgPath column to be not null and default to a specific path. In the table declaration, I have this:

[ImgPath] VARCHAR(256) NOT NULL 
    CONSTRAINT [DF_Users_ImgPath] DEFAULT ('/images/default.jpg')

I have a SaveUser stored procedure that is a merge statement to either update the user with the new ImgPath or to create a new user with the associated ImgPath.

CREATE PROCEDURE [dbo].[SaveUser] 
    (@UserId UNIQUEIDENTIFIER, 
     @ImgPath VARCHAR(256)) 
AS
    MERGE [dbo].[Users] AS TARGET
    USING (SELECT @UserId, @ImgPath) AS SOURCE ([UserId], [ImgPath]) ON (TARGET.[UserId] = SOURCE.[UserId])

    WHEN MATCHED THEN
        UPDATE 
            SET TARGET.[ImgPath] = SOURCE.[ImgPath]

    WHEN NOT MATCHED BY TARGET THEN
        INSERT ([UserId], [ImgPath])
        VALUES (SOURCE.[UserId], SOURCE.[ImgPath]);

How can I edit the stored procedure so that if ImgPath is null, it defaults to the DEFAULT CONSTRAINT value without having the value in both the table declaration and stored procedure?

If I send NULL or set the default value of @ImgPath to NULL, this does not work since NULL overrides the default value and since the column is not nullable, it throws an error.

COALESCE and ISNULL do not like DEFAULT being a fallback parameter.

I would also like to not have some nasty if/case statement that executes two MERGE statements.

A brute force solution would be to get the actual default value from the system information schemas. [This](https://stackoverflow.com/questions/141682/how-do-i-find-a-default-constraint-using-information-schema) question will lead you in that terrible direction. (`sys.default_constraints.definition` contains the value parenthetically.) _(I'd rather be diving.)_ — HABO, Apr 17 '19 at 22:16

JBJ · Answer 1 · 2019-04-18T15:47:04.307

Not as farmillar with MERGE as I should be, but this may work.

CREATE PROCEDURE [dbo].[SaveUser] 
    (@UserId UNIQUEIDENTIFIER, 
     @ImgPath VARCHAR(256)) 
AS
    MERGE [dbo].[Users] AS TARGET
    USING (SELECT @UserId, @ImgPath) AS SOURCE ([UserId], [ImgPath]) ON (TARGET.[UserId] = SOURCE.[UserId])

    WHEN MATCHED AND SOURCE.[ImgPath] is not null THEN
        UPDATE 
            SET TARGET.[ImgPath] = SOURCE.[ImgPath]


    WHEN NOT MATCHED BY TARGET AND SOURCE.[ImgPath] is not null THEN
        INSERT ([UserId], [ImgPath])
        VALUES (SOURCE.[UserId], SOURCE.[ImgPath])
    WHEN NOT MATCHED BY TARGET AND SOURCE.[ImgPath] IS NULL then
        INSERT ([UserId])
        VALUES (SOURCE.[UserId]);

This should only fiddle with ImgPath when ImgPath has a value.

This answer just removed the second WHEN MATCHED but doesn't account for when the user already exists and NULL is coming in for ImgPath. — ScubaSteve, Apr 18 '19 at 11:47

Stored procedure with parameter that defaults to default constraint value

1 Answers1