Как я могу предоставить значения параметров для хранимого процесса, вызываемого хранимым процессом (SQL Server)?

Основываясь на ответе здесь, у меня есть начало тестового SP, который я планирую в конечном итоге создать для вызова несколько раз, с большим количеством временных таблиц и различными значениями для параметра "Unit". Однако параметры @BegDate, @EndDate и @SortBy всегда будут одинаковыми - предоставленными пользователем.

Это то, что я до сих пор:

IF OBJECT_ID ( 'testSP', 'P' ) IS NOT NULL   
    DROP PROCEDURE testSP;  
GO
CREATE PROC [dbo].[testSP]
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20)
AS
BEGIN
    SELECT * FROM sys.databases
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "FirstUnit"; @BegDate = @BegDate;  @EndDate = @EndDate; @SortBy = @SortBy; ')

SELECT * FROM #MyTempTable

Мой вопрос: это правильный способ передачи параметров в существующую хранимую процедуру (SP2BCalled):

'EXEC SP2BCalled @Unit = "Abuelos"; @BegDate = @BegDate;  @EndDate = @EndDate; @SortBy = @SortBy; ')

Итак, вызываемый SP требует, чтобы ему были переданы параметры @BegDate, @EndDate и @SortBy, поэтому новый SP собирает их для передачи, но каков правильный синтаксис для этого?

Должен ли я сделать это так вместо этого:

CREATE PROC [dbo].[testSP]
    @BegDateLocal datetime,
    @EndDateLocal datetime,
    @SortByLocal varchar(20)

... а затем вызвать существующий SP примерно так:

SELECT * INTO #MyTempTable1 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "FirstUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

SELECT * INTO #MyTempTable2 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "SecondUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

SELECT * INTO #MyTempTable3 FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC SP2BCalled @Unit = "ThirdUnit"; @BegDate = @BegDateLocal;  @EndDate = @EndDateLocal; @SortBy = @SortByLocal; ')

...?, или как?

Затем мне нужно будет объединить все временные таблицы (#MyTempTable1, #MyTempTable2 и #MyTempTable3) и вернуть их в качестве окончательного результата.

ОБНОВИТЬ

Генрих Смит прав; когда я попытался запустить testSP, я получил:

Параметр конфигурации "Показать дополнительные параметры" изменен с 0 на 1. Запустите оператор RECONFIGURE для установки. Параметр конфигурации "Специальные распределенные запросы" изменен с 0 на 1. Запустите инструкцию RECONFIGURE для установки. Поставщик OLE DB "SQLNCLI11" для связанного сервера "(null)" вернул сообщение "Время ожидания входа истекло". Поставщик OLE DB "SQLNCLI11" для связанного сервера "(null)" возвратил сообщение "При установлении соединения с SQL Server произошла ошибка сети или конкретного экземпляра. Сервер не найден или недоступен. Проверьте правильность имени экземпляра и если SQL Server настроен для разрешения удаленных подключений. Для получения дополнительной информации см. Электронная документация по SQL Server."

Источник

4 ответа

Решение

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

Важно то, что вы не должны разделять параметры точкой с запятой. Вы должны использовать запятые.

'EXEC SP2BCalled @Unit = ''FirstUnit'', @BegDate = @BegDateLocal,  @EndDate = @EndDateLocal, @SortBy = @SortByLocal, ')
Источник

Если вы знаете, какова структура возвращаемого набора данных, вы можете использовать что-то вроде следующего:

--  Making assumptions about the data set
CREATE TABLE #MyTempTable1
 (
   Col1    int            not null
  ,Col2    datetime       not null
  ,ColEtc  nvarchar(max)  not null
 )

INSERT #MyTempTable1 (Col1, Col2, ColEtc)
 EXECUTE SP2BCalled
   @Unit    = 'Abuelos'
  ,@BegDate = @BegDate
  ,@EndDate = @EndDate
  ,@SortBy  = @SortBy
Источник

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

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''DRIVER={SQL Server}; Server=127.0.0.1;'',''EXEC [Test].[dbo].[Test] @var1 = ' + @var1 + ''')'
EXEC(@SQL)

Кроме того, из предоставленного кода

SELECT * INTO #MyTempTable FROM OPENROWSET(..

part никогда не будет выполняться как часть хранимой процедуры, так как есть несколько GO, которые заканчивают определение.

Вы также не указываете базу данных для использования в OPENROWSET,

Есть ли конкретная причина для использования OPENROWSETвместо использования полного доменного имени связанного сервера, [server].[database].[schema].[table]?

Источник

OPENROWSET принимает только буквальную строку. Чтобы использовать его, вам нужно собрать всю команду в динамическом SQL, а затем exec эта команда.

Временные таблицы, созданные внутри exec существуют только в рамках exec - они исчезают, когда он возвращается.

Вы можете использовать глобальные временные таблицы ##MyTempTable1 и т. Д., Но если несколько пользователей вызывают это одновременно, они все будут записывать в одну глобальную временную таблицу.
Трюк NEWID создает уникальную глобальную временную таблицу, чтобы избежать этого, но сложнее в управлении.

Используя двойную кавычку " работает только если SET QUOTED_IDENTIFIER выключен на удаленном сервере. Если он включен, то все в двойных кавычках не рассматривается как строка. Вместо этого оно рассматривается как имя объекта, например столбец или таблица. Чтобы не беспокоиться об этом, используйте пару одинарных кавычек '' представлять одну кавычку внутри строки. Тем не менее, обрабатывать вложенные кавычки таким способом непростительно.

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

stackru Имя базы данных, содержащей удаленный сохраненный процесс. Просто замените правильное имя для вас.

Declare @Unit varchar(max) = 'FirstUnit', @BegDateLocal datetime = '01-jun-2016', @EndDateLocal datetime = '30-jun-2016', @SortBy varchar(max) = 'X'
Declare @sql nvarchar(max)
Declare @exec nvarchar(max) = 'EXEC stackru.dbo.SP2BCalled @Unit = ''''FirstUnit'''', @BegDate = ''''' + convert(varchar(max), @BegDateLocal, 120) + ''''''
     + ', @EndDate = ''''' + convert(varchar(max), @EndDateLocal, 120) + ''''''
     + ', @SortBy = ''''' + @SortBy + ''''''
Declare @GlobalTemp varchar(max) = '##MyTempTable1'
Declare @GlobalTempNEWID varchar(max) = '[##' + cast(newid() as char(36)) + ']'
Declare @DropCheck varchar(max) = 'IF object_id(''tempdb..' + @GlobalTemp + ''') is not null drop table ' + @GlobalTemp
Set @sql = @DropCheck + '; SELECT * INTO ' + @GlobalTemp 
            + 'FROM OPENROWSET(
               ''SQLNCLI'',
               ''Server=(local)\SQL2008;Trusted_Connection=yes;'',' 
               + '''' + @exec + ''')'

Print @sql
exec (@sql)
SELECT * FROM ##MyTempTable1
Источник
Другие вопросы по тегам