Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Теряется табличный параметр при выполнении динамического запроса  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Здравствуйте, форумчане.

Есть скрипт - болванка для SP:

+
USE [SQLMVCPar]

BEGIN
  DECLARE @Tbl Table(
	[Row_No] [nvarchar](255),
	[Region_Cuntry] [nvarchar](255),
	[No_Cell] [nvarchar](255),
	[No_Fix] [nvarchar](255),
	[FLN] [nvarchar](255),
	[Name_Pos] [nvarchar](255),
	[Name_TO] [nvarchar](255),
	[Address] [nvarchar](255),
	[NROW] [bigint] 
    ) 

  DECLARE @Tbl2 Table (
     ID int,
	 NPage int,
	 NRow bigint
  )

	DECLARE @FilterColName sysname  -- Имя колонки, по которой будет выборка (фильтрация)
    DECLARE @OrderColName sysname   -- Имя колонки, по которой будет упорядочен НД
    DECLARE @FilterValue varchar(max) -- Значение - образец для фильтра
    DECLARE @OrderDirection varchar(4) -- ASC - по возрастанию, DESC - по убыванию
    DECLARE @SearchValue varchar(max)   -- Значение образца (если содержит символы % или *, то поиск Like, иначе - полное совпадение
	DECLARE @QRowsByPage int           -- Количество строк на странице

-- Шаг 1. С помощью SP UsP_SFN выбираются все записи Книги по указанным параметрам
--        Результат помещается во временную таблицу #Tmp1
  
  DECLARE @SQLSearch varchar(max)
  DECLARE @SearchVal varchar(max)
  DECLARE @SQL varchar(max)
  DECLARE @SQLExec varchar(max)



  SET @FilterColName = 'Name_TO'
  SET @OrderColName = 'FLN'
  SET @FilterValue = N'ГУ-Отделение ПФР по г. Москве и Московской области'   
  SET @OrderDirection = 'ASC'
  SET @SearchValue ='Уманский Борис Маркович'

  INSERT INTO @Tbl
  Exec UsP_SFN @FilterColName, @OrderColName, @FilterValue, @OrderDirection

  Select * from @Tbl

-- Шаг 2. Определяется выражение для поиска
  IF @SearchValue = '' 
     SET @SQLSearch = 'Where ' + @OrderColName + 'IS NULL' 
  ELSE
    BEGIN
	  SET @SearchVal = REPLACE(@SearchValue, '*', '%') -- Замена wildcard символа '*' на '%'
	  IF CHARINDEX('%',@SearchVal)>0
         SET @SQLSearch = 'Where ' + @OrderColName + ' LIKE ''' + @SearchVal + ''''
      ELSE
         SET @SQLSearch = 'Where ' + @OrderColName + ' = ''' + @SearchVal + ''''
	END

  -- Работает !
  INSERT INTO @Tbl2 SELECT TOP 1 CAST(Row_No as int) as ID, 0 as NPage, NROW as NRow FROM @Tbl Where FLN = 'Уманский Борис Маркович'

  -- Не работает !! 
  --  Сообщение 1087, уровень 15, состояние 2, строка 1
  --  Необходимо объявить табличную переменную "@Tbl2".
  --  Сообщение 1087, уровень 15, состояние 2, строка 1
  --  Необходимо объявить табличную переменную "@Tbl".
  exec ('INSERT INTO @Tbl2 SELECT TOP 1 CAST(Row_No as int) as ID, 0 as NPage, NROW as NRow FROM @Tbl Where FLN = ''Уманский Борис Маркович''')

  select * from @Tbl2

--  SET @SQLExec = 'SELECT TOP 1 CAST(Row_No as int) as ID, 0 as NPage, NROW as NRow FROM @Tbl ' + @SQLSearch
--  SET @SQL = 'INSERT INTO @Tbl2 '+@SQLExec

--  select @SQL

--  EXEC (@SQL)

--  Select * from @TT
--  SET @SQLExec = 'Exec UsP_SFN ''' + @FilterColName + ''',''' + @OrderColName +''',''' + @FilterValue + ''',''' + @OrderDirection + '';
--  SET @SQL = ' Select * into Tmp from OPENROWSET(''SQLNCLI'',''Server=MSGUNS-PC\MSSQLEXPRESS;Trusted_Connection=yes;'',N'''+ @SQLExec + ''');'

--  select @SQL
--  Exec (@SQL)

END


Комментариями я проставил сообщения об ошибке. Такое впечатление, что при выполнении динамического запроса сервер "забывает" об объявленных параметрах-таблицах. Подскажите, пожалуйста, где косяк.

Сообщение было отредактировано: 13 фев 20, 20:24
13 фев 20, 20:19    [22079565]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36639
Область видимости табличных переменных описана в документации.
И ваш код будет работать, если заменить табличную переменную на временную таблицу. Впрочем, об этом тоже написано в хелпе.

Сообщение было отредактировано: 13 фев 20, 20:25
13 фев 20, 20:25    [22079569]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Кажется разобрался :)

Дело в том, что динамический SQL выполняется как ОТДЕЛЬНЫЙ блок кода и параметров, объявленных в коде, его вызывающем, не видит. Т.е. в моем примере таблицы-параметры @Tbl и @Tbl2 находятся за пределами блока Exec, поэтому-то и не видны.

Заменил таблицы-параметры на временные #Tbl и #Tbl2 и все заработало.

Если будут комментарии, в т.ч. и критические, буду благодарен Мастерам. Розги, тухлые яйца и булыжники не приветствуются, но принимаются :)

Спасибо за любые советы и критику
13 фев 20, 20:52    [22079581]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Гавриленко Сергей Алексеевич,

Когда писал дополнение, Ваш пост не видел. Но он лишь подтвердил мою "догадку" :)
Спасибо, Серега :)
13 фев 20, 20:54    [22079583]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 46948
MsGuns
таблицы-параметры @Tbl и @Tbl2
Это никакие не таблицы-параметры.
Это табличные переменные.
13 фев 20, 22:02    [22079613]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
entrypoint
Member

Откуда:
Сообщений: 227
MsGuns,

DECLARE @t AS TABLE(i INT NOT NULL);

INSERT INTO @t(i)
EXECUTE ('SELECT 12345678')

SELECT i FROM @t;
14 фев 20, 10:20    [22079789]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
MsGuns
Member

Откуда:
Сообщений: 80
iap,
Это ясно, просто выразился неверно, но понимаю верно :) Главное, что с такими переменными можно работать как с обычными таблицами (Select,Insert,Update...), но при этом 'таблица' уничтожится при выходе из ХП. И никаких DROP не требуется.
14 фев 20, 17:13    [22080219]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
msLex
Member

Откуда:
Сообщений: 7620
MsGuns
iap,
Это ясно, просто выразился неверно, но понимаю верно :) Главное, что с такими переменными можно работать как с обычными таблицами (Select,Insert,Update...), но при этом 'таблица' уничтожится при выходе из ХП. И никаких DROP не требуется.

Так и # таблицы "уничтожится при выходе из ХП. И никаких DROP не требуется"
14 фев 20, 17:15    [22080223]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30460
msLex
MsGuns
iap,
Это ясно, просто выразился неверно, но понимаю верно :) Главное, что с такими переменными можно работать как с обычными таблицами (Select,Insert,Update...), но при этом 'таблица' уничтожится при выходе из ХП. И никаких DROP не требуется.

Так и # таблицы "уничтожится при выходе из ХП. И никаких DROP не требуется"
Притом, временные таблицы уничтожаются быстрее, если не делать DROP в конце процедур, если такая особенность у сиквела.
14 фев 20, 19:30    [22080339]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36639
alexeyvg,

Там все не так просто с закешированными планами. Суть: пока план есть, таблица точно есть, а на сдачу еще и данные есть.
Я где-то давно что-то на эту тему приводил, но это не точно.
15 фев 20, 02:43    [22080433]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7332
alexeyvg,

возможны ситуации, при которых сборщик мусора не успевает освобождать страницы tempdb по сравнению со скоростью создания этих таблиц. Это недостаток архитектуры приложения, но случай возможный. В такой ситуации прибегают к "синхронному" удалению таблиц, хотя это может привести к проявлению известного бага, который периодически лечат.
15 фев 20, 15:30    [22080502]     Ответить | Цитировать Сообщить модератору
 Re: Теряется табличный параметр при выполнении динамического запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30460
Владислав Колосов
alexeyvg,

возможны ситуации, при которых сборщик мусора не успевает освобождать страницы tempdb по сравнению со скоростью создания этих таблиц.
Спасибо, интересно, не знал про такое...

А как это проявляется, и какие могут быть последствия?
Что то вроде роста занятого места в tempdb, при высокой нагрузке?
15 фев 20, 15:57    [22080512]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить