Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
ЙИЛД_НУЛЛ
Guest
Тест 1.
create procedure dbo.up_test
as
begin
  select v.x.value('(//row/@value)[1]', 'varchar(3)')
  from (
    select 'ABC'+convert(varchar, null) value for xml raw, root, type
  ) v(x);
end;
go

SET CONCAT_NULL_YIELDS_NULL OFF
go
exec dbo.up_test;
go

Результат:

Msg 1934, Level 16, State 1, Procedure up_test, Line 45
SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns
and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Исправляем:
alter procedure dbo.up_test
as
begin
  SET CONCAT_NULL_YIELDS_NULL ON;
  select v.x.value('(//row/@value)[1]', 'varchar(3)')
  from (
    select 'ABC'+convert(varchar, null) value for xml raw, root, type
  ) v(x);
end;
go

SET CONCAT_NULL_YIELDS_NULL OFF
go
exec dbo.up_test;
go

- всё ок.

Тест 2.
create table dbo.tbl_test (
  id int not null primary key,
  name varchar(1)
)
go
create view dbo.vw_test
with schemabinding
as
select id, name from dbo.tbl_test
go
create unique clustered index idx$test on dbo.vw_test(id)
go

alter procedure dbo.up_test
as
begin
  SET CONCAT_NULL_YIELDS_NULL ON;
  select v.x.value('(//row/@value)[1]', 'varchar(3)')
  from (
    select 'ABC'+convert(varchar, null) value for xml raw, root, type
  ) v(x);
  delete dbo.tbl_test;
end;
go

SET CONCAT_NULL_YIELDS_NULL OFF
go
exec dbo.up_test;
go

- и снова та же самая ошибка, но на сей раз уже связанная не с SELECT, а с DELETE. И SET CONCAT_NULL_YIELDS_NULL ON в теле процедуры перед DELETE не помогает (но помогает он же перед вызовом всей процедуры). Вопрос - почему SET CONCAT_NULL_YIELDS_NULL ON в теле процедуры не учитывается при DML внутри процедуры?
P.S.
select @@version

---------------------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-CU5) (KB4013098) - 12.0.5546.0 (X64)
Apr 3 2017 14:55:37
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
4 авг 17, 13:37    [20700678]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
o-o
Guest
у вас в сессии зачем-то выставлен CONCAT_NULL_YIELDS_NULL OFF.
по умолчанию он вообще-то ON,
и никому не приходится его включать внутри процедуры.
и ваша первая процедура без всяких телодвижений не выдает никакую ошибку,
но именно у вас выдает, именно потому, что CONCAT_NULL_YIELDS_NULL OFF.

затем вы создаете indexed view все с тем же выключенным CONCAT_NULL_YIELDS_NULL.
зачем?
создавайте вью со влкюченной опцией,
и вообще включите и не трогайте ее больше
4 авг 17, 15:41    [20701258]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
ЙИЛД_НУЛЛ
Guest
o-o
у вас в сессии зачем-то выставлен CONCAT_NULL_YIELDS_NULL OFF.
по умолчанию он вообще-то ON

Представьте себе - есть приложения, у которых для непонятно каких целей после открытия соединения выставляется именно CONCAT_NULL_YIELDS_NULL OFF. Эти приложения можно заставить выполнять свои ХП, но ввиду значения этой настройки возможны сюрпризы. И поэтому внутри процедуры настройка выставляется в ON. Все вышеприведенные скрипты с SET CONCAT_NULL_YIELDS_NULL OFF - эмуляция действий в контексте соединения таких приложений.
А indexed view выше создается с вполне себе включенным CONCAT_NULL_YIELDS_NULL - он же по умолчанию включен, а второй тестовый скрипт - это скрипт с чистого листа, с дефолтными настройками.
И да, вопрос стартового постинга всё же немного о другом.
4 авг 17, 16:07    [20701402]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
invm
Member

Откуда: Москва
Сообщений: 9273
ЙИЛД_НУЛЛ
Вопрос - почему SET CONCAT_NULL_YIELDS_NULL ON в теле процедуры не учитывается при DML внутри процедуры?
Потому что при компиляции процедуры учитывается действуещее значение, а не которое будет действовать при выполнении. И delete dbo.tbl_test не может быть скомпилировано.

В случае манипуляций с xml - значение CONCAT_NULL_YIELDS_NULL проверяется при выполнении.
4 авг 17, 16:21    [20701471]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
ЙИЛД_НУЛЛ
Guest
invm
отому что при компиляции процедуры учитывается действуещее значение, а не которое будет действовать при выполнении. И delete dbo.tbl_test не может быть скомпилировано.

Ок, чтобы окончательно все прояснить - тестовый скрипт с нуля:
use stuff
go
SET CONCAT_NULL_YIELDS_NULL ON
go
drop view dbo.vw_test
go
drop table dbo.tbl_test
go
drop procedure up_test
go

create table dbo.tbl_test (
  id int not null primary key,
  name varchar(1)
)
go
create view dbo.vw_test
with schemabinding
as
select id, name from dbo.tbl_test
go
create unique clustered index idx$test on dbo.vw_test(id)
go

create procedure dbo.up_test
as
begin
  delete dbo.tbl_test;
end;
go

exec dbo.up_test
go
-- все в порядке
SET CONCAT_NULL_YIELDS_NULL OFF
go
exec dbo.up_test
go
-- ошибка 1934
SET CONCAT_NULL_YIELDS_NULL ON
go
exec dbo.up_test
go
-- и опять всё в порядке

т.е. получется, что при изменении опции CONCAT_NULL_YIELDS_NULL процедура перед вызовом перекомпилируется?
4 авг 17, 18:28    [20701856]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
invm
Member

Откуда: Москва
Сообщений: 9273
ЙИЛД_НУЛЛ
т.е. получется, что при изменении опции CONCAT_NULL_YIELDS_NULL процедура перед вызовом перекомпилируется?
Да. Потому что эта опция влияет на план выполнения.
4 авг 17, 18:33    [20701869]     Ответить | Цитировать Сообщить модератору
 Re: CONCAT_NULL_YIELDS_NULL в ХП - нюанс поведения  [new]
ЙИЛД_НУЛЛ
Guest
invm
ЙИЛД_НУЛЛ
т.е. получется, что при изменении опции CONCAT_NULL_YIELDS_NULL процедура перед вызовом перекомпилируется?
Да. Потому что эта опция влияет на план выполнения.

Ок, спасибо, вопрос исчерпан.
4 авг 17, 19:04    [20701917]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить