CODEWITHSUNDEEP
sql-servert-sqldefault-constraint
ScubaSteve
ScubaSteve

Reputation: 8270

Stored procedure with parameter that defaults to default constraint value

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.

Upvotes: 2

Views: 518

Answers (1)

JBJ
JBJ

Reputation: 403

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.

Upvotes: 0

Related Questions