SCOPE_IDENTITY, похоже, не работает с параметризованными запросами

#sql-server #vb.net #sql-insert

Вопрос:

У меня есть таблица со столбцом идентификатора, значение которого я хотел бы получить после ВСТАВКИ. Следующий код, который не использует параметры, работает идеально:

 string query = "INSERT INTO aTable ([aColumn]) VALUES (42)";
SqlCommand command = new SqlCommand(query, connection);
command.ExecuteNonQuery();

query = "SELECT CAST(SCOPE_IDENTITY() AS bigint)";
command = new SqlCommand(query, connection);
object identity = command.ExecuteScalar();

Если я изменю ВСТАВНУЮ часть приведенного выше кода для использования параметризованного запроса, ExecuteScalar() внезапно вернется System.DBNull значение. Вот как выглядит параметризованный код запроса:

 string query = "INSERT INTO aTable ([aColumn]) VALUES (@aColumn)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@aColumn", 42);
command.ExecuteNonQuery();

Я попытался изменить код SCOPE_IDENTITY , чтобы он использовал выходной параметр и вызывал ExecuteNonQuery() , но я все равно получаю нулевое значение в параметре out. Я также попытался запустить код для двух разных версий SQL Server (2012 и 2008, оба выпуска Express), снова с тем же результатом.

Есть идеи, что я здесь делаю не так?

1. Параметризованные запросы, вероятно, заканчиваются вызовом sp_executesql . Это выполняется в дочерней области и при выходе SCOPE_IDENTITY() имеет значение null во внешней области.

2. В дополнение к объединению их в один пакет, другим обходным решением было бы прекратить внедрение динамического SQL в ваше приложение и поместить эту логику в хранимую процедуру.

3. Вы также можете попытаться INSERT INTO aTable(...) OUTPUT Inserted.ID VALUES(.....) автоматически вывести все вставленные новые ID значения.

4. @MartinSmith Спасибо, это прекрасно объясняет, почему принятый мной ответ работает, а мой код-нет.

Ответ №1:

Попробуйте объединить ВСТАВКУ и ВЫБОР в одном операторе

 string query = "INSERT INTO aTable ([aColumn]) VALUES (@aColumn);SELECT CAST(SCOPE_IDENTITY() AS bigint)"; 
SqlCommand command = new SqlCommand(query, connection); 
command.Parameters.AddWithValue("@aColumn", 42); 
object identity = command.ExecuteScalar();