SQL Server - ВЫБРАТЬ ИЗ хранимой процедуры
У меня есть хранимая процедура, которая возвращает строки:
CREATE PROCEDURE MyProc
AS
BEGIN
SELECT * FROM MyTable
END
Моя настоящая процедура немного сложнее, поэтому необходим спрок.
Можно ли выбрать выход, вызвав эту процедуру?
Что-то вроде:
SELECT * FROM (EXEC MyProc) AS TEMP
Мне нужно использовать SELECT TOP X, ROW_NUMBERи дополнительный WHERE предложение на странице мои данные, и я не хочу передавать эти значения в качестве параметров.
17 ответов
Вы можете использовать пользовательскую функцию или представление вместо процедуры.
Процедура может возвращать несколько результирующих наборов, каждый со своей схемой. Это не подходит для использования в SELECT заявление.
Вы можете
- создайте табличную переменную для хранения результирующего набора из сохраненного процесса, а затем
- вставить вывод хранимого процесса в табличную переменную, а затем
- использовать переменную таблицы точно так же, как любую другую таблицу...
... sql....
Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params
Select * from @T Where ...
Вы должны взглянуть на эту прекрасную статью Эрланда Соммарского:
Это в основном перечисляет все доступные варианты для вашего сценария.
Вы хотите либо функцию с табличным значением, либо вставить свой EXEC во временную таблицу:
INSERT INTO #tab EXEC MyProc
Вам необходимо объявить тип таблицы, который содержит такое же количество столбцов, которое возвращает процедура хранилища. Типы данных столбцов в типе таблицы и столбцов, возвращаемых процедурами, должны быть одинаковыми
declare @MyTableType as table
(
FIRSTCOLUMN int
,.....
)
Затем вам нужно вставить результат вашей хранимой процедуры в тип таблицы, который вы только что определили
Insert into @MyTableType
EXEC [dbo].[MyStoredProcedure]
В конце просто выберите из вашего типа таблицы
Select * from @MyTableType
Вы должны прочитать об OPENROWSET и OPENQUERY
SELECT *
INTO #tmp FROM
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
Не обязательно использовать временную таблицу.
Это мое решение
SELECT * FROM
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue
Вы можете скопировать вывод из sp в временную таблицу.
CREATE TABLE #GetVersionValues
(
[Index] int,
[Name] sysname,
Internal_value int,
Character_Value sysname
)
INSERT #GetVersionValues EXEC master.dbo.xp_msver 'WindowsVersion'
SELECT * FROM #GetVersionValues
drop TABLE #GetVersionValues
Если "Доступ к данным" неверно,
EXEC sp_serveroption 'SQLSERVERNAME', 'DATA ACCESS', TRUE
после,
SELECT * FROM OPENQUERY(SQLSERVERNAME, 'EXEC DBNAME..MyProc @parameters')
оно работает.
Попробуйте преобразовать вашу процедуру во встроенную функцию, которая возвращает таблицу следующим образом:
CREATE FUNCTION MyProc()
RETURNS TABLE AS
RETURN (SELECT * FROM MyTable)
И тогда вы можете назвать это как
SELECT * FROM MyProc()
У вас также есть возможность передачи параметров в функцию следующим образом:
CREATE FUNCTION FuncName (@para1 para1_type, @para2 para2_type , ... )
И называть это
SELECT * FROM FuncName ( @para1 , @para2 )
Используйте OPENQUERY и перед исполнением set 'SET FMTONLY OFF; ВКЛЮЧИТЕ NOCOUNT;'
Попробуйте этот пример кода:
SELECT top(1)*
FROM
OPENQUERY( [Server], 'SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE [database].[dbo].[storedprocedure] value,value ')
Вы можете немного обмануть с помощью OPENROWSET:
SELECT ...fieldlist...
FROM OPENROWSET('SQLNCLI', 'connection string', 'name of sp')
WHERE ...
Конечно, каждый раз запускается весь SP.
Для простоты и возможности повторного запуска я использовал систему StoredProcedure sp_readerrorlog для получения данных:
-----USING Table Variable
DECLARE @tblVar TABLE (
LogDate DATETIME,
ProcessInfo NVARCHAR(MAX),
[Text] NVARCHAR(MAX)
)
INSERT INTO @tblVar Exec sp_readerrorlog
SELECT LogDate as DateOccured, ProcessInfo as pInfo, [Text] as Message FROM @tblVar
-----(OR): Using Temp Table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (
LogDate DATETIME,
ProcessInfo NVARCHAR(55),
Text NVARCHAR(MAX)
)
INSERT INTO #temp EXEC sp_readerrorlog
SELECT * FROM #temp
Похоже, вам просто нужно использовать представление. Представление позволяет представить запрос в виде таблицы, чтобы его можно было запрашивать.
Если ваш сервер называется SERVERX, например, я так и сделал...
EXEC sp_serveroption 'SERVERX', 'DATA ACCESS', TRUE;
DECLARE @CMD VARCHAR(1000);
DECLARE @StudentID CHAR(10);
SET @StudentID = 'STUDENT01';
SET @CMD = 'SELECT * FROM OPENQUERY([SERVERX], ''SET FMTONLY OFF; SET NOCOUNT ON; EXECUTE MYDATABASE.dbo.MYSTOREDPROC ' + @StudentID + ''') WHERE SOMEFIELD = SOMEVALUE';
EXEC (@CMD);
Чтобы проверить это сработало, я закомментировал EXEC() командной строки и заменил его SELECT @CMD просмотреть команду, прежде чем пытаться ее выполнить! Это должно было гарантировать, что все правильное количество одинарных кавычек было в правильном месте.:-)
Я надеюсь, что это помогает кому-то.
Я считаю это полезным. Реальный пример.
declare @tempv1 table (Number int, AccountType varchar(255), DisplayName varchar(255), Kind int, UsagePageBreak int, PrimoPrev money, PrevPeriod money, UltimoPrev money, Primo money, Debit money, Credit money, Period money, Ultimo money, ToCurrentDate money, IndexUltimoPct money, IndexPeriodPct money, UltimoPrevOK int, UltimoOK int)
declare @tempv2 table (Number int, AccountType varchar(255), DisplayName varchar(255), Kind int, UsagePageBreak int, PrimoPrev money, PrevPeriod money, UltimoPrev money, Primo money, Debit money, Credit money, Period money, Ultimo money, ToCurrentDate money, IndexUltimoPct money, IndexPeriodPct money, UltimoPrevOK int, UltimoOK int)
insert into @tempv1
exec sp_reports_Accounting_BalanceV2
@fromaccount=1010,
@toaccount=1010,
@fromfinancialdate = '2021-01-01 00:00:00 +01:00',
@tofinancialdate = '2021-12-31 00:00:00+01:00',
@includezero=0,@ouids=NULL,@currentOrganizationalUnitId=1,@currentuserid=1,@includenotbooked=0
insert into @tempv2
exec sp_reports_Accounting_BalanceV3
@fromaccount=1010,
@toaccount=1010,
@fromfinancialdate = '2021-01-01 00:00:00 +01:00',
@tofinancialdate = '2021-12-31 00:00:00+01:00',
@includezero=0,@ouids=NULL,@currentOrganizationalUnitId=1,@currentuserid=1,@includenotbooked=0
select * from @tempv1 except select * from @tempv2
union all
select * from @tempv2 except select * from @tempv1
Спустя несколько часов и 131 поисковый запрос в Google выяснилось, что все, что мне действительно нужно, это:
MyProcedure -- this is so simple it doesn't look like an executable line of code so here's a comment to make it look more substantial
(без EXEC, без SELECT, без скобок...)
Нажмите F5/Выполнить — и вы получите таблицу результатов.
Итак, чтобы уточнить: просто укажите имя процедуры - если вам нужно ввести параметры, поставьте пробел, а затем параметры, которые вы хотите передать, через запятую, если их несколько.
Пример с параметрами:
MyProcedure 'Hello', 1
(Для varchar необходимы одинарные кавычки; для битовых и целых чисел кавычки отсутствуют.)