хранимой - хранимые процедуры sql server примеры




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

Самое простое решение:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

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

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

Как сделать SELECT * INTO [temp table] FROM [stored procedure] ? Не FROM [Table] и без определения [temp table] ?

Select все данные из BusinessLine в tmpBusLine отлично.

select *
into tmpBusLine
from BusinessLine

Я пытаюсь сделать то же самое, но использование stored procedure которая возвращает данные, не совсем то же самое.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

Выходное сообщение:

Msg 156, уровень 15, состояние 1, строка 2 Некорректный синтаксис рядом с ключевым словом «exec».

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


  1. Я создаю таблицу со следующей схемой и данными.
  2. Создайте хранимую процедуру.
  3. Теперь я знаю, каков результат моей процедуры, поэтому я выполняю следующий запрос.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
    CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
    
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF
    

    VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] Вкл.

    create procedure GetDate
    as
    begin
        select Id,ParentId from tblTestingTree
    end
    
    create table tbltemp
    (
        id int,
        ParentId int
    )
    insert into tbltemp
    exec GetDate
    
    select * from tbltemp;
    

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

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

Для этого вы можете использовать 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

Если вам посчастливилось иметь SQL 2012 или выше, вы можете использовать dm_exec_describe_first_result_set_for_object

Я только что отредактировал sql, предоставленный gotqn. Спасибо gotqn.

Это создает глобальную таблицу temp с именем, аналогичным имени процедуры. После этого временную таблицу можно использовать по мере необходимости. Просто не забудьте бросить его перед повторным выполнением.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end

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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Если таблица результатов вашего хранимого процесса слишком сложна для ввода инструкции «create table» вручную, и вы не можете использовать OPENQUERY или OPENROWSET, вы можете использовать sp_help для создания списка столбцов и типов данных для вас. Когда у вас есть список столбцов, это просто вопрос форматирования в соответствии с вашими потребностями.

Шаг 1. Добавьте «в #temp» к выходному запросу (например, «выберите [...] в #temp из [...]»).

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

Шаг 2. Запустите sp_help в таблице temp. (например, «exec tempdb..sp_help #temp»)

После создания таблицы temp запустите sp_help в таблице temp, чтобы получить список столбцов и типов данных, включая размер полей varchar.

Шаг 3. Скопируйте столбцы и типы данных в инструкцию create table

У меня есть лист Excel, который я использую для форматирования вывода sp_help в оператор «create table». Вам не нужно ничего подобного, просто скопируйте и вставьте в свой редактор SQL. Используйте имена столбцов, их размеры и типы для создания инструкции «Создать таблицу #x [...]» или «declare @x table [...]», которую вы можете использовать для INSERT результатов хранимой процедуры.

Шаг 4: Вставка во вновь созданную таблицу

Теперь у вас будет запрос, похожий на другие решения, описанные в этом потоке.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

Этот метод также можно использовать для преобразования таблицы temp ( #temp ) в переменную таблицы ( @temp ). Хотя это может быть больше шагов, чем просто написать инструкцию create table самостоятельно, это предотвращает ручную ошибку, такую ​​как опечатки и несоответствия типа данных в больших процессах. Отладка опечатки может занять больше времени, чем писать запрос в первую очередь.


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

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


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

  1. только первая строка набора хранимой процедуры может быть вставлена ​​во временную таблицу
  2. хранимая процедура не должна выполнять динамический оператор T-SQL ( sp_executesql )
  3. вам нужно сначала определить структуру временной таблицы

Вышеприведенное может выглядеть как ограничение, но ИМХО это прекрасно имеет смысл - если вы используете sp_executesql вы можете сразу вернуть два столбца и один раз десять, а если у вас есть несколько наборов результатов, вы также не можете вставлять их в несколько таблиц - вы можете вставить максимум в двух таблицах в одном выражении T-SQL (используя предложение OUTPUT и триггеры).

Таким образом, проблема заключается главным образом в том, как определить временную структуру таблицы перед выполнением инструкции EXEC ... INTO ...

Первый работает с OBJECT_ID а второй и третий - с Ad-hoc-запросами. Я предпочитаю использовать DMV вместо sp, так как вы можете использовать CROSS APPLY и одновременно создавать временные определения таблиц для нескольких процедур.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

Также обратите внимание на поле system_type_name поскольку это может быть очень полезно. Он хранит полное определение столбца. Например:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

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

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

Обратите внимание, что указанные выше объекты не могут определить первые данные набора результатов в некоторых cases например, когда выполняются динамические инструкции T-SQL или временные таблицы используются в хранимой процедуре.


Эта сохраненная процедура выполняет задание:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

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

Если вы хотите, чтобы он работал с временной таблицей, вам нужно будет использовать таблицу ##GLOBAL и затем ее отбросить.


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

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

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


Код

CREATE TABLE #T1
(
    col1 INT NOT NULL,
    col2 NCHAR(50) NOT NULL,
    col3 TEXT NOT NULL,
    col4 DATETIME NULL,
    col5 NCHAR(50) NULL,
    col6 CHAR(2) NULL,
    col6 NCHAR(100) NULL,
    col7 INT NULL,
    col8 NCHAR(50) NULL,
    col9 DATETIME NULL,
    col10 DATETIME NULL
)

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)


SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col6,
    col7,
    col8,
    col9,
    col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

Надеюсь, это поможет. Пожалуйста, при необходимости.


Ну, вам нужно создать временную таблицу, но она не должна иметь правильную схему ... Я создал хранимую процедуру, которая изменяет существующую временную таблицу, чтобы она имела необходимые столбцы с правильными данными тип и порядок (удаление всех существующих столбцов, добавление новых столбцов):

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Обратите внимание, что это не будет работать, если sys.dm_exec_describe_first_result_set_for_object не сможет определить результаты хранимой процедуры (например, если она использует временную таблицу).


Я бы сделал следующее

  1. Создайте (преобразуйте SP в) UDF (значение таблицы UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


Это можно сделать в SQL Server 2014+, если SP возвращает только одну таблицу. Если кто-то найдет способ сделать это для нескольких таблиц, я хотел бы узнать об этом.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

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

Существуют также варианты этого, которые также работают с Dynamic SQL.


Я встретил ту же проблему, и вот что я сделал для этого из предложения Павла . Основная часть здесь заключается в том, NEWID()чтобы избегать нескольких пользователей одновременно запускать процедуры хранения / сценарии, боль для глобальной временной таблицы.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;




stored-procedures