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

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

Есть SP, которая по полученным параметрам делает выборку, сортировку
и нумерацию записей из некоторой таблицы БД. Вот она:

USE [SQLMVCPar]
GO
/****** Object:  StoredProcedure [dbo].[UsP_SFN]    Script Date: 12.02.2020 12:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Извлекает записи из полной телефонной Книги с применением указанного фильтра,
-- упорядочивает их по указанной колонке, нумерует их по порядку (с 1),
-- добавляет в хвост НД колонку NROW, куда помещает порядковый номер строки 
ALTER PROCEDURE [dbo].[UsP_SFN]
	@FilterColName sysname,  -- Имя колонки, по которой будет выборка (фильтрация)
    @OrderColName sysname,   -- Имя колонки, по которой будет упорядочен НД
    @FilterValue varchar(max), -- Значение - образец для фильтра
    @OrderDirection varchar(4) -- ASC - по возрастанию, DESC - по убыванию

AS
BEGIN
  SET NOCOUNT ON;

DECLARE @SQLStr nvarchar(2000)

IF OBJECT_ID (N'Tmp', N'U') IS NOT NULL DROP TABLE Tmp

  SET @SQLStr = N'SELECT Row_No ,Region_Cuntry, No_Cell, No_Fix, FLN, Name_Pos, Name_TO, [Address],
      ROW_NUMBER() OVER(ORDER BY ' + @OrderColName + ' ' + @OrderDirection + ') as NROW INTO Tmp FROM _TelBook '
  IF @FilterColName > ''
     SET @SQLStr = @SQLStr + N'WHERE ' + @FilterColName + ' = ''' + @FilterValue + ''''

--  SELECT @SQLStr
  EXEC (@SQLStr)
  
  SELECT * from Tmp
  DROP TABLE Tmp

END

Все работает отлично.

Требуется создать SP, которая будет выбирать из НД, полученного вышеуказанной SP, нужную страницу

Вот скрипт - контент для этой SP

--sp_configure 'show advanced options', 1;  
--RECONFIGURE;
--GO 
--sp_configure 'Ad Hoc Distributed Queries', 1;  
--RECONFIGURE;  
--GO  

use SQLMVCPar
begin

  DECLARE @FilterColName sysname
  DECLARE @OrderColName sysname
  DECLARE @FilterValue varchar(max)
  DECLARE @OrderDirection varchar(4)

  DECLARE @NPage int
  DECLARE @QRowsAtPage int
  DECLARE @SQLExec varchar(max)
  DECLARE @SQL varchar(max)

  SET @FilterColName = 'Name_TO'
  SET @OrderColName = 'FLN'
  SET @FilterValue = N'Филиал в пгт. Пеньково'
  SET @OrderDirection = 'ASC'

  SET @NPage = 1
  SET @QRowsAtPage = 10


  IF OBJECT_ID (N'Tmp', N'U') IS NOT NULL DROP TABLE Tmp

  CREATE TABLE dbo.Tmp(
	[Row_No] [nvarchar](255) NULL,
	[Region_Cuntry] [nvarchar](255) NULL,
	[No_Cell] [nvarchar](255) NULL,
	[No_Fix] [nvarchar](255) NULL,
	[FLN] [nvarchar](255) NULL,
	[Name_Pos] [nvarchar](255) NULL,
	[Name_TO] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[NROW] [bigint] NULL
    ) ON [PRIMARY]

  INSERT INTO dbo.Tmp
  Exec UsP_SFN @FilterColName, @OrderColName, @FilterValue, @OrderDirection

--  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)

  SELECT * from tmp
    Where NROW > (@NPage-1)*@QRowsAtPage AND NROW < @NPage*@QRowsAtPage + 1
  DROP TABLE tmp

end

При запуске выдает:

Сообщение 556, уровень 16, состояние 3, строка 44
Ошибка при выполнении INSERT EXEC, поскольку хранимая процедура изменила схему целевой таблицы.

Почитал, что такая ошибка возникает из-за особенностей очистки MS SQL Server хранилища
(https://support.microsoft.com/ru-ru/help/4465511/error-556-insert-exec-failed-stored-procedure-altered-table-schema)

Также почитал о "подводных камнях Insert Exec" здесь:
https://olontsev.ru/2016/05/insert-exec-behaviour/

Как избавиться от ошибки я так и не понял (видимо из-за "особенностей" автоперевода на сайте Microsoft)
Решил пойти другим путем: использовать OPENROWSET, опыта работы с которым совсем не имею.
Но возникла новая проблема: дело в том, что "базовая" SP требует параметры-строки. Попытка вставить их в строку для OpenRowset привела к тому, что вместо трех параметров - строк, OpenRowSet получает 7 и естественно опять ошибка.

Как решить проблему ? Замена базовой SP на функцию невозможна из-за динамического SQL
12 фев 20, 14:15    [22078363]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
andy st
Member

Откуда:
Сообщений: 782
MsGuns,
DROP TABLE Tmp
в процедуре, которая пишет в эту же таблицу?
даже страшно предположить, что вы там курите
12 фев 20, 14:20    [22078368]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2360
MsGuns,

#tmp
12 фев 20, 14:41    [22078394]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Спасибо огромное !
А ларчик просто открывался :)

Дурные отрыжки копипасты :)
Ну и замена постоянной таблицы на временную тоже..

Трава обычная :)
Надо обойти кривости мсскл, в частности невозможность использования в UDF динамического SQL, а также вызова SP из UDF. Все приходится делать в хранимках.

Таблица создается как временное хранилище для того, чтобы всунуть туда выборку и вернуть ее вовне. После чего таблица удаляется. Что-то типа курсора :) Т.к. все это в рамках одной транзакции, то конфликтов вроде как быть не должно.
12 фев 20, 15:02    [22078412]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3415
MsGuns
Спасибо огромное !
А ларчик просто открывался :)

Дурные отрыжки копипасты :)
Ну и замена постоянной таблицы на временную тоже..

Трава обычная :)
Надо обойти кривости мсскл, в частности невозможность использования в UDF динамического SQL, а также вызова SP из UDF. Все приходится делать в хранимках.

Таблица создается как временное хранилище для того, чтобы всунуть туда выборку и вернуть ее вовне. После чего таблица удаляется. Что-то типа курсора :) Т.к. все это в рамках одной транзакции, то конфликтов вроде как быть не должно.


"кривой" мсскл грустно курит в углу бычки и наблюдает как чёткие девелоперы курят траву и обходят его "кривости"
12 фев 20, 15:43    [22078450]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
Владислав Колосов
Member

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

в частности невозможность использования в UDF динамического SQL, а также вызова SP из UDF


да нет, же забористая у вас трава. Вы откуда образование разработчика СУБД получили - из Pascal или Basic?
12 фев 20, 17:11    [22078581]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
MsGuns
Member

Откуда:
Сообщений: 80
>да нет, же забористая у вас трава. Вы откуда образование разработчика СУБД получили - из Pascal или Basic?

1. Какое отношение ЯП имеет к СУБД ?
2. Если речь идет о СУБД, то мой опыт такой: Clipper, Paradox, Interbase(Firebird), MS SQL, Oracle, PostgreSQL, MySQL
Правда, экспертом ни в одной из этих СУБД не являюсь. Просто пришлось работать с этими дядьками.
3. Пилюля получена и съедена. А где эффект, т.е. где ссылка (хотя бы) на разъяснение каков я дурак ?
Или плюнул и пошел ?
12 фев 20, 17:57    [22078648]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Читаем https://docs.microsoft.com/ru-ru/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver15
Особенно п.5 "Ограничений".
12 фев 20, 18:05    [22078656]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
Владислав Колосов
Member

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

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

  SET @SQLStr = N'SELECT Row_No ,Region_Cuntry, No_Cell, No_Fix, FLN, Name_Pos, Name_TO, [Address],
      ROW_NUMBER() OVER(ORDER BY ' + @OrderColName + ' ' + @OrderDirection + ') as NROW INTO Tmp FROM _TelBook '


такие запросы плохо работают при массовой нагрузке на сервер, хотя и допустимы при редких единичных выполнениях. Произвольная сортировка и нумерация строк довольно часто выполняется в клиентском приложении, т.к. это работает быстрее как с точки зрения извлечения данных, так и с точки зрения сортировки.
12 фев 20, 18:12    [22078667]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
MsGuns
Member

Откуда:
Сообщений: 80
Исходная "таблица" - это что-то вроде Корпоративной телефонной Книги с массой дополнительных атрибутов. Вся эта "кухня" хранится не в одной таблице, конечно, и даже не в десяти. В итоге записей в этой книге несколько сот тысяч. Число пользователей приложения исчисляется тысячами в один момент времени. Выборка всей Книги не нужна в принципе - поэтому используются "фильтры" (Where), число которых весьма велико (более 20). Писать в UDF все эти кейсы - это ж... Тем более, что так и есть в настоящий момент. В результате UDF-ки огроменные и их дофига и больше. Я хочу несколько "облегчить" логику пусть даже ценою некоторой потери производительности. Проект и так слишком "тяжел" и на его сопровождение и развитие уходит до черта ресурсов (читай - денег). В конце концов докупить пару серверов много проще, чем найти квалифицированного программиста взамен ушедшего.
12 фев 20, 19:12    [22078717]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
msLex
Member

Откуда:
Сообщений: 7620
MsGuns
Исходная "таблица" - это что-то вроде Корпоративной телефонной Книги с массой дополнительных атрибутов. Вся эта "кухня" хранится не в одной таблице, конечно, и даже не в десяти. В итоге записей в этой книге несколько сот тысяч. Число пользователей приложения исчисляется тысячами в один момент времени. Выборка всей Книги не нужна в принципе - поэтому используются "фильтры" (Where), число которых весьма велико (более 20). Писать в UDF все эти кейсы - это ж... Тем более, что так и есть в настоящий момент. В результате UDF-ки огроменные и их дофига и больше. Я хочу несколько "облегчить" логику пусть даже ценою некоторой потери производительности. Проект и так слишком "тяжел" и на его сопровождение и развитие уходит до черта ресурсов (читай - денег). В конце концов докупить пару серверов много проще, чем найти квалифицированного программиста взамен ушедшего.

Замените udf на процедуры и делайте там свои динамические запросы.
12 фев 20, 19:16    [22078721]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с INSERT INTO EXEC SP (MS SQL Server 2012 MSSQLEXPRESS)  [new]
MsGuns
Member

Откуда:
Сообщений: 80
msLex
MsGuns
Исходная "таблица" - это что-то вроде Корпоративной телефонной Книги с массой дополнительных атрибутов. Вся эта "кухня" хранится не в одной таблице, конечно, и даже не в десяти. В итоге записей в этой книге несколько сот тысяч. Число пользователей приложения исчисляется тысячами в один момент времени. Выборка всей Книги не нужна в принципе - поэтому используются "фильтры" (Where), число которых весьма велико (более 20). Писать в UDF все эти кейсы - это ж... Тем более, что так и есть в настоящий момент. В результате UDF-ки огроменные и их дофига и больше. Я хочу несколько "облегчить" логику пусть даже ценою некоторой потери производительности. Проект и так слишком "тяжел" и на его сопровождение и развитие уходит до черта ресурсов (читай - денег). В конце концов докупить пару серверов много проще, чем найти квалифицированного программиста взамен ушедшего.

Замените udf на процедуры и делайте там свои динамические запросы.


А вот Владислав Колосов придерживается противоположного мнения :)
12 фев 20, 20:13    [22078774]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить