Хранимая процедура SQL-сервера возвращает таблицу

Ответ 1

Процедура не может вернуть таблицу как таковую. Однако вы можете выбрать из таблицы в процедуре и направить ее в таблицу (или переменную таблицы) следующим образом:

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t

Ответ 2

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

https://msdn.microsoft.com/en-us/library/ms186755.aspx

Основное различие между функцией и процедурой заключается в том, что функция не вносит никаких изменений в какую-либо таблицу. Он возвращает только значение.

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

Вероятно, есть много способов очистить это. Но он хорошо иллюстрирует функцию.

USE Northwind

CREATE FUNCTION usp_listFields(@schema VARCHAR(50), @table VARCHAR(50))
RETURNS @query TABLE (
    FieldName VARCHAR(255)
    )
BEGIN
    INSERT @query
    SELECT
        'SELECT ''' + @table+'~'+RTRIM(COLUMN_NAME)+'~''+CONVERT(VARCHAR, COUNT(*)) '+
    'FROM '[email protected]+'.'[email protected]+' '+
          ' WHERE isnull("'+RTRIM(COLUMN_NAME)+'",'''')<>'''' UNION'
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table and TABLE_SCHEMA = @schema
    RETURN
END

Затем выполнение функции с

SELECT * FROM usp_listFields('Employees')

создает ряд строк, таких как:

SELECT 'Employees~EmployeeID~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("EmployeeID",'')<>'' UNION
SELECT 'Employees~LastName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("LastName",'')<>'' UNION
SELECT 'Employees~FirstName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees  WHERE isnull("FirstName",'')<>'' UNION

Ответ 3

Вы можете использовать параметр out вместо возвращаемого значения, если вы хотите как набор результатов, так и возвращаемое значение

CREATE PROCEDURE proc_name 
@param int out
AS
BEGIN
    SET @param = value
SELECT ... FROM [Table] WHERE Condition
END
GO

Ответ 4

Я делаю это часто, используя типы таблиц, чтобы обеспечить более согласованность и упростить код. Вы не можете технически вернуть "таблицу", но вы можете вернуть набор результатов и использовать синтаксис INSERT INTO.. EXEC..., вы можете явно вызвать PROC и сохранить результаты в виде таблицы. В следующем примере я фактически передаю таблицу в PROC вместе с другим параметром, мне нужно добавить логику, тогда я эффективно "возвращаю таблицу" и затем могу работать с ней как с табличной переменной.

/****** Check if my table type and/or proc exists and drop them ******/
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'returnTableTypeData')
DROP PROCEDURE returnTableTypeData
GO
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'myTableType')
DROP TYPE myTableType
GO

/****** Create the type that I'll pass into the proc and return from it ******/
CREATE TYPE [dbo].[myTableType] AS TABLE(
    [someInt] [int] NULL,
    [somenVarChar] [nvarchar](100) NULL
)
GO

CREATE PROC returnTableTypeData
    @someInputInt INT,
    @myInputTable myTableType READONLY --Must be readonly because
AS
BEGIN

    --Return the subset of data consistent with the type
    SELECT
        *
    FROM
        @myInputTable
    WHERE
        someInt < @someInputInt

END
GO


DECLARE @myInputTableOrig myTableType
DECLARE @myUpdatedTable myTableType

INSERT INTO @myInputTableOrig ( someInt,somenVarChar )
VALUES ( 0, N'Value 0' ), ( 1, N'Value 1' ), ( 2, N'Value 2' )

INSERT INTO @myUpdatedTable EXEC returnTableTypeData @someInputInt=1, @[email protected]

SELECT * FROM @myUpdatedTable


DROP PROCEDURE returnTableTypeData
GO
DROP TYPE myTableType
GO

Ответ 5

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

Из урока 2: Разработка хранимых процедур:

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

Если вам все еще нужна таблица, возвращаемая из SP, вам придется либо выполнить набор записей, возвращенный из SELECT в SP, либо связать его с переменной OUTPUT, которая передает тип данных XML.

НТН,

Джон

Ответ 6

У меня была аналогичная ситуация и решалась с помощью таблицы temp внутри процедуры, при этом те же поля возвращались исходной хранимой процедурой:

CREATE PROCEDURE mynewstoredprocedure
AS 
BEGIN

INSERT INTO temptable (field1, field2)
EXEC mystoredprocedure @param1, @param2

select field1, field2 from temptable

-- (mystoredprocedure returns field1, field2)

END

Ответ 7

create procedure PSaleCForms
as
begin
declare 
@b varchar(9),
@c nvarchar(500),
@q nvarchar(max)
declare @T table(FY nvarchar(9),Qtr int,title nvarchar    (max),invoicenumber     nvarchar(max),invoicedate datetime,sp decimal    18,2),grandtotal decimal(18,2))
declare @data cursor
set @data= Cursor
forward_only static
for 
select x.DBTitle,y.CurrentFinancialYear from [Accounts     Manager].dbo.DBManager x inner join [Accounts Manager].dbo.Accounts y on        y.DBID=x.DBID where x.cfy=1
open @data
fetch next from @data
into @c,@b
while @@FETCH_STATUS=0
begin
set @q=N'Select '''[email protected]+''' [fy], case cast(month(i.invoicedate)/3.1 as int)     when 0 then 4 else cast(month(i.invoicedate)/3.1 as int) end [Qtr],     l.title,i.invoicenumber,i.invoicedate,i.sp,i.grandtotal from     ['[email protected]+'].dbo.invoicemain i inner join  ['[email protected]+'].dbo.ledgermain l on     l.ledgerid=i.ledgerid where (sp=0 or stocktype=''x'') and invoicetype=''DS'''

вставить в @T exec [master].dbo.sp_executesql @q fetch next from @data в @c, @b end закрыть @data deallocate @data select * from @T return end

Ответ 8

Вот пример SP, который возвращает таблицу и возвращаемое значение. Я не знаю, нужно ли вам вернуть "Возвращаемое значение", и я понятия не имею о MATLAB и о том, что он требует.

CREATE PROCEDURE test
AS 
BEGIN

    SELECT * FROM sys.databases

    RETURN 27
END

--Use this to test
DECLARE @returnval int

EXEC @returnval = test 

SELECT @returnval