Вы можете сделать это, используя 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
Если Вы хотите сделать это без первого объявления временной таблицы, Вы могли бы попытаться создать пользовательскую функцию, а не хранимая процедура и заставить ту пользовательскую функцию возвратить таблицу. Alternativly, если Вы хотите использовать хранимую процедуру, пробуют что-то вроде этого:
CREATE TABLE #tmpBus
(
COL1 INT,
COL2 INT
)
INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE
SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
В SQL Server 2005 можно использовать INSERT INTO ... EXEC
для вставки результата хранимой процедуры в таблицу. От MSDN INSERT
документация (для SQL Server 2000, на самом деле):
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
Когда хранимая процедура возвращает много столбцов, и Вы не хотите вручную "составлять" временную таблицу для содержания результата, я нашел, что самый легкий путь состоит в том, чтобы войти в хранимую процедуру и добавить "в" пункт на последнем избранном операторе и добавить 1=0 к где пункт.
Выполнение хранимая процедура однажды и возвращается и удаляет код SQL, который Вы просто добавили. Теперь, у Вас будет пустая таблица, соответствующая результату хранимой процедуры. Вы могли или "написать сценарий таблицы, как создают" для временной таблицы или просто вставляют непосредственно в ту таблицу.
Несколько лет поздно к вопросу, но мне было нужно что-то вроде этого для некоторой быстрой и грязной генерации кода. Я верю, поскольку другие заявили, что просто легче определить временную таблицу впереди, но этот метод должен работать на простые запросы хранимой процедуры или 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
Снова, я только протестировал его с простыми запросами хранимой процедуры и простыми запросами, таким образом, Ваш пробег может варьироваться. Надежда это помогает кому-то.
Для этого можно использовать 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
Я нашел Передача массивов / таблиц данных в хранимые процедуры , который может дать вам другое представление о том, как вы можете решить вашу проблему.
Ссылка предлагает использовать Параметр типа изображения для передачи в хранимую процедуру. Затем в хранимой процедуре изображение преобразуется в табличную переменную, содержащую исходные данные.
Возможно, есть способ использовать это с временной таблицей.
Это ответ на слегка измененную версию вашего вопроса. Если вы можете отказаться от использования хранимой процедуры для пользовательской функции, вы можете использовать встроенную пользовательскую функцию, возвращающую табличное значение. По сути, это хранимая процедура (принимает параметры), которая возвращает таблицу в качестве набора результатов; и поэтому хорошо сочетается с оператором 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 исключает использование хранимых процедур, возвращающих более одного набора результатов. Этого можно достичь, заключив несколько встроенных пользовательских функций табличных значений в одну хранимую процедуру.
Ваша хранимая процедура только извлекает данные или изменяет их тоже? Если он используется только для извлечения, вы можете преобразовать хранимую процедуру в функцию и использовать общие табличные выражения (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)
Другой метод - создать тип и использовать PIPELINED для последующей передачи вашего объекта. Однако это ограничивается знанием столбцов. Но у него есть преимущество:
SELECT *
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))
Куассной помог мне добиться этого, но не хватало одного:
Итак, я нашел способ работать с системой, и мне также не нужно было делать определение таблицы таким жестким и переопределять его внутри другой хранимой процедуры (и конечно рискните может сломаться)!
Да, вы можете динамически создать определение таблицы, возвращаемое из хранимой процедуры с помощью , используя оператор 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, вызываемая процедура не может получить доступ к тому, что вы заблокировали. То есть, если вы не будете осторожны, вы заблокируете себя.
Подключение не бесплатно, поэтому снижается производительность.