Вставить значение по умолчанию, если параметр равен нулю

Насколько мне известно, значение по умолчанию вставляется только в том случае, если вы не указываете значение в операторе вставки. Так, например, вам нужно будет сделать что-то вроде следующего в таблице с тремя полями (значение 2 по умолчанию)

INSERT INTO t (value1, value3) VALUES ('value1', 'value3')

И тогда значение2 будет по умолчанию. Может быть, кто-нибудь подскажет, как это сделать для таблицы с одним полем.

Вы можете использовать значения по умолчанию для параметров хранимых процедур:

CREATE PROCEDURE MyTestProcedure ( @MyParam1 INT,
@MyParam2 VARCHAR(20) = ‘ABC’,
@MyParam3 INT = NULL)
AS
BEGIN
    -- Procedure body here

END

Если @ MyParam2 не указан, он будет иметь значение ABC ...

Не указывайте столбец или значение при вставке, и значение константы DEFAULT будет заменено отсутствующим значением.

Я не знаю, как это будет работать в таблице с одним столбцом. Я имею в виду: было бы, но было бы не очень полезно.

Самый простой способ сделать это - изменить объявление таблицы на

CREATE TABLE Demo
(
    MyColumn VARCHAR(10) NOT NULL DEFAULT 'Me'
)

Теперь в вашей хранимой процедуре вы можете сделать что-то вроде.

CREATE PROCEDURE InsertDemo
    @MyColumn VARCHAR(10) = null
AS
INSERT INTO Demo (MyColumn) VALUES(@MyColumn)

Однако этот метод работает ТОЛЬКО в том случае, если у вас не может быть null, иначе вашей хранимой процедуре придется использовать другую форму вставки для запуска значения по умолчанию.

Попробуйте оператор if ...

if @value is null 
    insert into t (value) values (default)
else
    insert into t (value) values (@value)

Кристоф,

Значение по умолчанию для столбца применяется только в том случае, если вы не укажете столбец в инструкции INSERT.

Поскольку вы явно перечисляете столбец в своем операторе вставки и явно устанавливаете его на NULL, это переопределяет значение по умолчанию для этого столбца

Что вам нужно сделать, это «если в ваш sproc передан нуль, не пытайтесь вставить для этого столбца».

Это быстрый и неприятный пример того, как это сделать с помощью динамического sql.

Создайте таблицу с несколькими столбцами со значениями по умолчанию ...

CREATE TABLE myTable (
    always VARCHAR(50),
    value1 VARCHAR(50) DEFAULT ('defaultcol1'),
    value2 VARCHAR(50) DEFAULT ('defaultcol2'),
    value3 VARCHAR(50) DEFAULT ('defaultcol3')
)

Создайте SPROC, который динамически создает и выполняет ваш оператор вставки на основе входных параметров.

ALTER PROCEDURE t_insert (
    @always VARCHAR(50),
    @value1 VARCHAR(50) = NULL,
    @value2 VARCHAR(50) = NULL,
    @value3 VARCAHR(50) = NULL
)
AS 
BEGIN
DECLARE @insertpart VARCHAR(500)
DECLARE @valuepart VARCHAR(500)

SET @insertpart = 'INSERT INTO myTable ('
SET @valuepart = 'VALUES ('

    IF @value1 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value1,'
        SET @valuepart = @valuepart + '''' + @value1 + ''', '
    END

    IF @value2 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value2,'
        SET @valuepart = @valuepart + '''' + @value2 + ''', '
    END

    IF @value3 IS NOT NULL
    BEGIN
        SET @insertpart = @insertpart + 'value3,'
        SET @valuepart = @valuepart + '''' + @value3 + ''', '
    END

    SET @insertpart = @insertpart + 'always) '
    SET @valuepart = @valuepart + + '''' + @always + ''')'

--print @insertpart + @valuepart
EXEC (@insertpart + @valuepart)
END

Следующие 2 команды должны дать вам пример того, что вы хотите в качестве выходных данных ...

EXEC t_insert 'alwaysvalue'
SELECT * FROM  myTable

EXEC t_insert 'alwaysvalue', 'val1'
SELECT * FROM  myTable

EXEC t_insert 'alwaysvalue', 'val1', 'val2', 'val3'
SELECT * FROM  myTable

Я знаю, что это очень запутанный способ делать то, что вам нужно. Вероятно, вы могли бы в равной степени выбрать значение по умолчанию из InformationSchema для соответствующих столбцов, но, честно говоря, я мог бы подумать о том, чтобы просто добавить значение по умолчанию в param в верхней части процедуры

Запрашивающий должен узнать разницу между предоставленным пустым значением и нулевым значением.

Другие отправили правильный базовый ответ: предоставленное значение, включая ноль, - что-нибудь, и поэтому оно используется. По умолчанию предоставляется ТОЛЬКО значение, если оно не указано. Но настоящая проблема здесь - непонимание значения null.

.

Вы можете использовать функцию COALESCE в MS SQL.

INSERT INTO t (value) VALUES (COALESCE (@value, 'something'))

Лично я не без ума от этого решения, так как это кошмар обслуживания, если вы хотите изменить значение по умолчанию.

Я бы предпочел предложение Mitchel Sellers, но оно не работает в MS SQL. Не могу разговаривать с другими СУБД SQL.

Возможно, это не самый удобный для производительности способ, но вы могли бы создать скалярную функцию, которая извлекает из информационной схемы имя таблицы и столбца, а затем вызывать ее, используя логику isnull, которую вы пробовали ранее:

    CREATE FUNCTION GetDefaultValue
    (
        @TableName VARCHAR(200),
        @ColumnName VARCHAR(200)
    )
    RETURNS VARCHAR(200)
    AS
    BEGIN
        -- you'd probably want to have different functions for different data types if
        -- you go this route
    RETURN (SELECT TOP 1 REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '') 
            FROM information_schema.columns
            WHERE table_name = @TableName AND column_name = @ColumnName)

    END
    GO

А затем назовите это так:

INSERT INTO t (value) VALUES ( ISNULL(@value, SELECT dbo.GetDefaultValue('t', 'value') )

Надеюсь помочь новичку, как и я, тем, кто использует операторы Upsert в MSSQL .. (Этот код, который я использовал в своем проекте на MSSQL 2008 R2, работает просто идеально ... Может быть, это не лучшая практика .. Статистика времени выполнения показывает выполнение время как 15 миллисекунд с оператором вставки)

Просто установите поле «Значение по умолчанию или привязка» в качестве значения, которое вы решите использовать в качестве значения по умолчанию для своего столбца, а также установите для столбца значение «Не принимать нулевые значения из меню дизайна» и создайте этот сохраненный протокол.

`USE [YourTable]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[YourTableName]

    @Value smallint,
    @Value1 bigint,
    @Value2 varchar(50),
    @Value3 varchar(20),
    @Value4 varchar(20),
    @Value5 date,
    @Value6 varchar(50),
    @Value7 tinyint,
    @Value8 tinyint,
    @Value9 varchar(20),
    @Value10 varchar(20),
    @Value11 varchar(250),
    @Value12 tinyint,
    @Value13 varbinary(max) 

- в моем проекте @ Value13 - столбец с фотографиями, который хранится в виде байтового массива .. - И я планировал использовать фотографию по умолчанию, когда фотография не передана --сп хранить в db ..

AS
--SET NOCOUNT ON
IF @Value = 0 BEGIN
    INSERT INTO YourTableName (
        [TableColumn1],
        [TableColumn2],
        [TableColumn3],
        [TableColumn4],
        [TableColumn5],
        [TableColumn6],
        [TableColumn7],
        [TableColumn8],
        [TableColumn9],
        [TableColumn10],
        [TableColumn11],
        [TableColumn12],
        [TableColumn13]
    )
    VALUES (
        @Value1,
        @Value2,
        @Value3,
        @Value4,
        @Value5,
        @Value6,
        @Value7,
        @Value8,
        @Value9,
        @Value10,
        @Value11,
        @Value12,
        default
    )
    SELECT SCOPE_IDENTITY() As InsertedID
END
ELSE BEGIN
    UPDATE YourTableName SET 
        [TableColumn1] = @Value1,
        [TableColumn2] = @Value2,
        [TableColumn3] = @Value3,
        [TableColumn4] = @Value4,
        [TableColumn5] = @Value5,
        [TableColumn6] = @Value6,
        [TableColumn7] = @Value7,
        [TableColumn8] = @Value8,
        [TableColumn9] = @Value9,
        [TableColumn10] = @Value10,
        [TableColumn11] = @Value11,
        [TableColumn12] = @Value12,
        [TableColumn13] = @Value13
    WHERE [TableColumn] = @Value

END
GO`

Это лучшее, что я могу придумать. Это предотвращает внедрение SQL с использованием только одного оператора вставки и может быть расширено с помощью большего количества операторов case.

CREATE PROCEDURE t_insert (     @value varchar(50) = null )
as
DECLARE @sQuery NVARCHAR (MAX);
SET @sQuery = N'
insert into __t (value) values ( '+
CASE WHEN @value IS NULL THEN ' default ' ELSE ' @value ' END +' );';

EXEC sp_executesql 
@stmt = @sQuery, 
@params = N'@value varchar(50)',
@value = @value;

GO

Имея в таблице достаточное количество значений по умолчанию, вы можете просто сказать:

INSERT t DEFAULT VALUES

Однако учтите, что это маловероятный случай.

Мне пришлось использовать его только один раз в производственной среде. У нас были две тесно связанные таблицы, и нам нужно было гарантировать, что ни одна из таблиц не имеет одинакового уникального идентификатора, поэтому у нас была отдельная таблица, в которой был только столбец идентификаторов, и лучший способ вставить в нее был с синтаксисом выше.

chrisofspades,

Насколько я знаю, это поведение несовместимо с тем, как работает движок db, но есть простое (я не знаю, элегантное, но производительное) решение для достижения ваших двух целей НЕ

1. сохранить значение по умолчанию в нескольких местах и
2. используйте несколько операторов вставки.

Решение состоит в том, чтобы использовать два поля, одно допускающее значение NULL для вставки, а другое рассчитанное для выборок:

CREATE TABLE t (
    insValue VARCHAR(50) NULL
    , selValue AS ISNULL(insValue, 'something')
)

DECLARE @d VARCHAR(10)
INSERT INTO t (insValue) VALUES (@d) -- null
SELECT selValue FROM t

Этот метод даже позволяет вам централизовать управление бизнес-настройками по умолчанию в таблице параметров, поместив для этого специальную функцию с изменением vg:

selValue AS ISNULL(insValue, 'something')

для

selValue AS ISNULL(insValue, **getDef(t,1)**)

Надеюсь, это поможет.

Самое краткое решение, которое я мог придумать, - это следовать за вставкой с обновлением столбца со значением по умолчанию:

IF OBJECT_ID('tempdb..#mytest') IS NOT NULL DROP TABLE #mytest
CREATE TABLE #mytest(f1 INT DEFAULT(1), f2 INT)
INSERT INTO  #mytest(f1,f2) VALUES (NULL,2)
INSERT INTO  #mytest(f1,f2) VALUES (3,3)

UPDATE #mytest SET f1 = DEFAULT WHERE f1 IS NULL

SELECT * FROM #mytest

На сегодняшний день лучшим вариантом является создание триггера INSTEAD OF INSERT для вашей таблицы, удаление значений по умолчанию из вашей таблицы и перемещение их в триггер.

Это будет выглядеть так:

create trigger dbo.OnInsertIntoT
ON TablenameT
INSTEAD OF INSERT
AS
insert into TablenameT
select
   IsNull(column1 ,<default_value>)
  ,IsNull(column2 ,<default_value>)
  ...
from inserted

Это заставляет его работать НЕ ВАЖНО, какой код пытается вставить NULL в вашу таблицу, избегает хранимых процедур, полностью прозрачен, и вам нужно только поддерживать значения по умолчанию в одном месте, а именно в этом триггере.

Обычно я использую шаблон, чтобы создать строку без столбцов, которые имеют ограничения по умолчанию, а затем обновить столбцы, чтобы заменить значения по умолчанию предоставленными значениями (если не null).

Предполагая, что col1 является первичным ключом, а col4 и col5 имеют ограничение по умолчанию

-- create initial row with default values
insert table1 (col1, col2, col3)
    values (@col1, @col2, @col3)

-- update default values, if supplied
update table1
    set col4 = isnull(@col4, col4),
        col5 = isnull(@col5, col5)
    where col1 = @col1

Если вы хотите, чтобы фактические значения по умолчанию были занесены в таблицу ...

-- create initial row with default values
insert table1 (col1, col2, col3)
    values (@col1, @col2, @col3)

-- create a container to hold the values actually inserted into the table
declare @inserted table (col4 datetime, col5 varchar(50))

-- update default values, if supplied
update table1
    set col4 = isnull(@col4, col4),
        col5 = isnull(@col5, col5)
    output inserted.col4, inserted.col5 into @inserted (col4, col5)
    where col1 = @col1

-- get the values defaulted into the table (optional)
select @col4 = col4, @col5 = col5 from @inserted

Ваше здоровье...