Вставьте результаты хранимой процедуры во временную таблицу

Вы можете сделать это, используя SET NOEXEC ON. Это указывает SQL Server игнорировать все SQL-коды до достижения SET NOEXEC OFF.

IF EXISTS (SELECT *
          FROM INFORMATION_SCHEMA.ROUTINES
          WHERE ROUTINE_TYPE = 'PROCEDURE'
          AND ROUTINE_SCHEMA = 'dbo'
          AND ROUTINE_NAME = 'HelloWorld')
BEGIN
    SET NOEXEC ON
END
GO

CREATE PROCEDURE dbo.HelloWorld
AS
    PRINT 'Hello world'
GO

SET NOEXEC OFF
GO
1478
задан DineshDB 26 March 2018 в 06:07
поделиться

11 ответов

Если Вы хотите сделать это без первого объявления временной таблицы, Вы могли бы попытаться создать пользовательскую функцию, а не хранимая процедура и заставить ту пользовательскую функцию возвратить таблицу. Alternativly, если Вы хотите использовать хранимую процедуру, пробуют что-то вроде этого:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
589
ответ дан Peter Mortensen 26 March 2018 в 16:07
поделиться
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
126
ответ дан Tigerjz32 26 March 2018 в 16:07
поделиться

В SQL Server 2005 можно использовать INSERT INTO ... EXEC для вставки результата хранимой процедуры в таблицу. От MSDN INSERT документация (для SQL Server 2000, на самом деле):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
286
ответ дан Michael Berkowski 26 March 2018 в 16:07
поделиться

Когда хранимая процедура возвращает много столбцов, и Вы не хотите вручную "составлять" временную таблицу для содержания результата, я нашел, что самый легкий путь состоит в том, чтобы войти в хранимую процедуру и добавить "в" пункт на последнем избранном операторе и добавить 1=0 к где пункт.

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

101
ответ дан Peter Mortensen 26 March 2018 в 16:07
поделиться

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

Это будет немного замысловатым, но это одалживает от участников здесь, а также решение Paul White от Exchange Стопки DBA Получают типы столбца результата хранимой процедуры . Снова, для повторения этого подхода & пример не разработан для процессов в пользовательской среде. В этом случае определение таблицы устанавливается в течение короткого времени в глобальной временной таблице для ссылки шаблонным процессом генерации кода.

я не полностью протестировал это, таким образом, могут быть протесты, таким образом, можно хотеть перейти к ссылке MSDN в ответе Paul White. Это относится к SQL 2012 и выше.

Первое использование хранимая процедура sp_describe_first_result_set , которая напоминает Oracle, описывает.

Это оценит первую строку первого набора результатов поэтому, если Ваша хранимая процедура или оператор возвратят несколько запросов, то это только опишет первый результат.

я создал сохраненный proc для разрушения задач, который возвращает единственное поле для выбора из создать временное определение таблицы.

CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
     @sql NVARCHAR(4000)
    ,@table_name VARCHAR(100)
    ,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TempTableDefinition NVARCHAR(MAX)
    DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)

    DECLARE @ResultDefinition TABLE (  --The View Definition per MSDN
      is_hidden         bit NOT NULL
    , column_ordinal    int NOT NULL
    , [name]            sysname NULL
    , is_nullable       bit NOT NULL
    , system_type_id    int NOT NULL
    , system_type_name  nvarchar(256) NULL
    , max_length        smallint NOT NULL
    , [precision]       tinyint NOT NULL
    , scale             tinyint NOT NULL
    , collation_name    sysname NULL    
    , user_type_id      int NULL
    , user_type_database    sysname NULL    
    , user_type_schema  sysname NULL
    , user_type_name    sysname NULL    
    , assembly_qualified_type_name      nvarchar(4000)  
    , xml_collection_id         int NULL
    , xml_collection_database   sysname NULL    
    , xml_collection_schema     sysname NULL    
    , xml_collection_name       sysname NULL
    , is_xml_document           bit NOT NULL            
    , is_case_sensitive         bit NOT NULL            
    , is_fixed_length_clr_type  bit NOT NULL    
    , source_server             sysname NULL            
    , source_database           sysname NULL
    , source_schema             sysname NULL
    , source_table              sysname NULL
    , source_column             sysname NULL
    , is_identity_column        bit NULL
    , is_part_of_unique_key     bit NULL
    , is_updateable             bit NULL
    , is_computed_column        bit NULL
    , is_sparse_column_set      bit NULL
    , ordinal_in_order_by_list  smallint NULL   
    , order_by_is_descending    smallint NULL   
    , order_by_list_length      smallint NULL
    , tds_type_id               int NOT NULL
    , tds_length                int NOT NULL
    , tds_collation_id          int NULL
    , tds_collation_sort_id     tinyint NULL
    )

    --Insert the description into table variable    
    INSERT @ResultDefinition
    EXEC sp_describe_first_result_set @sql

    --Now Build the string to create the table via union select statement
    ;WITH STMT AS (
        SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
        UNION ALL

        SELECT 
         CONCAT(
                CASE column_ordinal
                    WHEN 1 THEN '     ' ELSE '   , ' END  --Determines if comma should precede
                , QUOTENAME([name]) , '   ', system_type_name  -- Column Name and SQL TYPE
                ,CASE is_nullable 
                    WHEN 0 THEN '   NOT NULL' ELSE '   NULL' END --NULLABLE CONSTRAINT          
               ) AS TextVal
        FROM @ResultDefinition WHERE is_hidden = 0  -- May not be needed
        UNION ALL

        SELECT N');' + @NewLine
    ) 

    --Now Combine the rows to a single String
    SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT

    SELECT @TableDefinition = @TempTableDefinition
END

загадка - то, что необходимо использовать глобальную таблицу, но необходимо сделать ее достаточно уникальной, таким образом, можно отбросить и часто создавать из нее, не волнуясь о коллизии.
В примере я использовал Гуид (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) для глобальной переменной, заменяющей дефисы подчеркиванием

DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql can be a stored procedure name like dbo.foo without parameters

DECLARE @TableDef NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet] 
    @sql, @GlobalTempTable, @TableDef OUTPUT

--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef 

--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
    EXEC sp_executesql @sql 

--Select the results into your undefined Temp Table from the Global Table
SELECT * 
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT * FROM #MyTempTable

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

Снова, я только протестировал его с простыми запросами хранимой процедуры и простыми запросами, таким образом, Ваш пробег может варьироваться. Надежда это помогает кому-то.

1
ответ дан 22 November 2019 в 20:15
поделиться

Для этого можно использовать OPENROWSET . Взглянуть. Я также включил код sp_configure, чтобы включить специальные распределенные запросы, если он еще не включен.

CREATE PROC getBusinessLineHistory
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 getBusinessLineHistory')

SELECT * FROM #MyTempTable
683
ответ дан 22 November 2019 в 20:15
поделиться

Я нашел Передача массивов / таблиц данных в хранимые процедуры , который может дать вам другое представление о том, как вы можете решить вашу проблему.

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

Возможно, есть способ использовать это с временной таблицей.

11
ответ дан 22 November 2019 в 20:15
поделиться

Это ответ на слегка измененную версию вашего вопроса. Если вы можете отказаться от использования хранимой процедуры для пользовательской функции, вы можете использовать встроенную пользовательскую функцию, возвращающую табличное значение. По сути, это хранимая процедура (принимает параметры), которая возвращает таблицу в качестве набора результатов; и поэтому хорошо сочетается с оператором INTO.

Вот хорошая быстрая статья об этом и других пользовательских функциях. Если у вас все еще есть потребность в хранимой процедуре, вы можете обернуть встроенную возвращающую табличное значение пользовательскую функцию хранимой процедурой. Хранимая процедура просто передает параметры, когда вызывает select * из встроенной возвращающей табличное значение пользовательской функции.

Так, например, вы d иметь встроенную возвращающую табличное значение пользовательскую функцию для получения списка клиентов для определенного региона:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Затем вы можете вызвать эту функцию, чтобы получить ваши результаты, например:

SELECT * FROM CustomersbyRegion(1)

Или выполнить SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Если вам все еще нужна хранимая процедура, оберните функцию как таковую:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я думаю, что это самый «безхакерный» метод для получения желаемых результатов. Он использует существующие функции, поскольку они были предназначены для использования без дополнительных сложностей. Вложив в хранимую процедуру встроенную возвращающую табличное значение пользовательскую функцию, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использование OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Затем вы можете вызвать эту функцию, чтобы получить результат, например:

SELECT * FROM CustomersbyRegion(1)

Или сделать SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Если вам все еще нужна хранимая процедура, оберните функцию как таковую:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я думаю это самый «беззащитный» метод для получения желаемых результатов. Он использует существующие функции, поскольку они были предназначены для использования без дополнительных сложностей. Вложив в хранимую процедуру встроенную возвращающую табличное значение пользовательскую функцию, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Затем вы можете вызвать эту функцию, чтобы получить результат, например:

SELECT * FROM CustomersbyRegion(1)

Или выполнить SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Если вам все еще нужна хранимая процедура, оберните функцию как таковую:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я думаю это самый «беззащитный» метод для получения желаемых результатов. Он использует существующие функции, поскольку они были предназначены для использования без дополнительных сложностей. Вложив в хранимую процедуру встроенную возвращающую табличное значение пользовательскую функцию, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я считаю, что это наиболее «безхакерный» метод получения желаемых результатов. Он использует существующие функции, поскольку они были предназначены для использования без дополнительных сложностей. Вложив в хранимую процедуру встроенную возвращающую табличное значение пользовательскую функцию, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использование OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я считаю, что это наиболее «безхакерный» метод получения желаемых результатов. Он использует существующие функции, поскольку они были предназначены для использования без дополнительных сложностей. Вложив в хранимую процедуру встроенную возвращающую табличное значение пользовательскую функцию, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация у вас есть доступ к функциям двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация у вас есть доступ к функциям двумя способами. Плюс! У вас есть только одна точка обслуживания для фактического кода SQL.

Было предложено использование OPENROWSET, но это не то, для чего функция OPENROWSET предназначалась для использования (Из электронной документации):

Включает все соединения Информация что требуется для доступа к удаленным данным из источника данных OLE DB. Эта метод является альтернативой доступу таблиц на связанном сервере и является разовый, специальный метод подключения и доступ к удаленным данным с помощью OLE БД. Для более частых ссылок на Источники данных OLE DB, используйте связанные серверы вместо этого.

Использование OPENROWSET выполнит свою работу, но потребует дополнительных накладных расходов на открытие локальных соединений и маршалинг данных. Это также может быть не во всех случаях, поскольку для этого требуется разрешение на специальный запрос, что создает угрозу безопасности и, следовательно, может быть нежелательным. Кроме того, подход OPENROWSET исключает использование хранимых процедур, возвращающих более одного набора результатов. Этого можно достичь, заключив несколько встроенных пользовательских функций табличных значений в одну хранимую процедуру.

191
ответ дан 22 November 2019 в 20:15
поделиться

Ваша хранимая процедура только извлекает данные или изменяет их тоже? Если он используется только для извлечения, вы можете преобразовать хранимую процедуру в функцию и использовать общие табличные выражения (CTE), не объявляя их, как показано ниже:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

Однако следует использовать все, что необходимо получить из CTE. только в одном заявлении. Вы не можете выполнить с температурой как ... и попробуйте использовать его после пары строк SQL. Вы можете иметь несколько CTE в одном операторе для более сложных запросов.

Например,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)
47
ответ дан 22 November 2019 в 20:15
поделиться

Другой метод - создать тип и использовать PIPELINED для последующей передачи вашего объекта. Однако это ограничивается знанием столбцов. Но у него есть преимущество:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))
9
ответ дан 22 November 2019 в 20:15
поделиться

Куассной помог мне добиться этого, но не хватало одного:

**** Мне нужно было использовать параметры в хранимой процедуре. ****

И OPENQUERY не допускает этого:

Итак, я нашел способ работать с системой, и мне также не нужно было делать определение таблицы таким жестким и переопределять его внутри другой хранимой процедуры (и конечно рискните может сломаться)!

Да, вы можете динамически создать определение таблицы, возвращаемое из хранимой процедуры с помощью , используя оператор OPENQUERY с фиктивными переменными (до тех пор, пока NO RESULT SET возвращает такое же количество полей и в той же позиции, что и набор данных с хорошими данными).

После создания таблицы вы можете использовать хранимую процедуру exec во временной таблице в течение всего дня.


И, чтобы отметить (как указано выше), вы должны разрешить доступ к данным,

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

Код:

declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
  'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

Спасибо за информацию, которая была предоставлена ​​изначально ... Да, наконец, мне не нужно создавать все эти фиктивные (строгие) определения таблиц при использовании данных из другой хранимой процедуры или базы данных, и да вы также можете использовать параметры.

Поиск ссылочных тегов:

  • Хранимая процедура SQL 2005 во временной таблице

  • openquery с хранимой процедурой и переменными 2005

  • openquery с переменными

  • выполнение хранимой процедуры во временной таблице

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

Замечание об ошибке : это не будет работать с временными таблицами , http://www.sommarskog.se/share_data.html#OPENQUERY

Ссылка: следующее: определить LOCALSERVER. В примере это может выглядеть как ключевое слово, но на самом деле это всего лишь имя. Вот как вы это делаете:

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

Чтобы создать связанный сервер, вы должны иметь разрешение ALTER ANY SERVER или быть членом любой из фиксированных ролей сервера sysadmin или setupadmin.

OPENQUERY открывает новое соединение с SQL Server. Это имеет некоторые последствия:

Процедура, которую вы вызываете с OPENQUERY, не может ссылаться на временные таблицы, созданные в текущем соединении.

Новое соединение имеет свою собственную базу данных по умолчанию (определенную с помощью sp_addlinkedserver, по умолчанию - master), поэтому все спецификации объекта должны включать имя базы данных.

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

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

30
ответ дан 22 November 2019 в 20:15
поделиться
Другие вопросы по тегам:

Похожие вопросы: