1

What I'm trying to do is to have an optional parameter in my stored procedure that gets the default value defined in the table if not provided.

create table primates (
    id      int identity(1,1) primary key,
    genus   varchar(32) not null default 'gorilla'
);
go


create procedure spawn_primate
    @genus varchar(32) = default
as begin
    set nocount on;
    insert into primates 
            (genus) 
        values 
            (@genus);
end;
go


exec spawn_primate
-- Cannot insert the value NULL into column 'genus', table 'my_db.dbo.primates'; column does not allow nulls. INSERT fails.

In the code above, I want @genus to be equal to gorilla if not provided, but SQL Server treats it as NULL. Well, I can set the default value to be the one I used in the table like so: @genus varchar(32) = 'gorilla', but then I have to keep an eye on my default values and make sure they are the same everywhere, which is a bit of a hassle...

Is there a (preferably clean) way to do this? Maybe I can check if @genus is NULL and get the default value from underlying system tables and set it, but am not sure if it's the best way.

By the way, what's the difference between = NULL and = default?

Improve this question
1

2 Answers 2

Reset to default
3

You don't need to explicitly specify the default value to use it. So change your stored procedure to:

create procedure spawn_primate
    @genus varchar(32) = NULL
as begin
    set nocount on;
    if (@genus is NULL)
       insert into primates default values
    else
       insert into primates (genus) values (@genus)
end;
go

This will work if ALL columns have their defaults assigned during table creation. If some of the columns will not have default you will get an error.

If you want to use partly default values and partly explicitly specified then list the latter via

insert into primates (col1,col2) values (col1_value, col2_value).

In the above case columns id and genus will have their default values but new columns col1 and col2 will have their values of col1_value and col2_value respectively.

Improve this answer
6
  • what if I have a third column, like name nvarchar(32) not null without a default value and want to insert provided name + default genus? Commented Oct 28 at 15:07
  • @Shahriar just do as in my second example: insert into primates (third_column_name) values (@third_column_value). This will insert explicit value for the @third_column_name column and defaults for PK and genus. Replace @third_column_name with the name of your 3rd column and @third_column_value with the actual value you are inserting or input param. Commented Oct 28 at 15:18
  • Oh you mean 2 inserts, one conditional insert for some columns based on provided values, and one simple insert for the rest of the columns after that? Commented Oct 28 at 15:28
  • @Shahriar it can be just 1 insert if you are inserting at least 1 non-default value. Commented Oct 28 at 15:38
  • 1
    Courtesy fiddle. See also alt constructions Commented Oct 29 at 1:57
1

Assuming you had multiple (or many) such columns, you could use dynamic SQL to build your INSERT statement.

So you build up a string of the form

insert into table
    (col1, col2)
values
    (@col1, @col2);

missing out any values which are null

In cases where no values are given you can use

insert into table
    default values;

create procedure spawn_primate
    @genus varchar(32) = null,
    @name varchar(100) = null
as

set nocount, xact_abort on;

declare @sql nvarchar(max) = N'
insert into primates';

select @sql += isnull(N'
    (' + string_agg(col, ', ') + ')
values
    (' + string_agg('@' + col, ', ') + ')', '
  default values') + ';'
from (
    select N'genus' where @genus is not null
    union all
    select N'name'  where @name  is not null
) t(col);

print @sql;    -- your friend

exec sp_executesql @sql,
    N'@genus varchar(32),
      @name varchar(100)',
    @genus = @genus,
    @name = @name;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.