Procédure SQL server stockée retourner une table

envisagez de créer une fonction qui peut retourner une table et être utilisée dans une requête.

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

la principale différence entre une fonction et une procédure est qu'une fonction ne modifie aucune table. Il ne renvoie une valeur.

dans cet exemple je crée une requête pour me donner les comptes de toutes les colonnes d'une table donnée qui ne sont pas nulles ou vides.

Il y a probablement de nombreuses façons de nettoyer cette. Mais cela illustre bien une fonction.

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 '+@schema+'.'+@table+' '+
          ' WHERE isnull("'+RTRIM(COLUMN_NAME)+'",'''')<>'''' UNION'
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table and TABLE_SCHEMA = @schema
    RETURN
END

alors exécutant la fonction avec

SELECT * FROM usp_listFields('Employees')

produit un certain nombre de lignes comme:

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

je le fais souvent en utilisant des types de Table pour assurer plus de cohérence et simplifier le code. Vous ne pouvez pas techniquement retourner "une table", mais vous pouvez retourner un jeu de résultats et en utilisant INSERT INTO .. EXEC ... syntaxe, vous pouvez clairement appeler un PROC et stocker les résultats dans un type de table. Dans l'exemple suivant, je suis en fait passer une table dans un PROC avec un autre param, je dois ajouter la logique, puis je suis effectivement "retourner une table" et peut ensuite travailler avec cela comme une variable de table.

/****** 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, @myInputTable=@myInputTableOrig

SELECT * FROM @myUpdatedTable


DROP PROCEDURE returnTableTypeData
GO
DROP TYPE myTableType
GO

Vous pouvez utiliser un paramètre out au lieu de la valeur return si vous voulez à la fois un résultat et une valeur return

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

la valeur D'État renvoyée par une procédure stockée ne peut être qu'un type de données INT. Vous ne pouvez pas retourner d'autres types de données dans le rapport de retour.

Leçon 2: Concevoir Des Procédures Stockées:

Chaque procédure stockée renvoie un entier valeur connue comme le valeur d'état d'exécution ou code de retour.

si vous voulez toujours qu'une table soit retournée par le SP, vous devrez soit travailler le jeu d'enregistrement renvoie à partir d'un SELECT dans le SP ou lie dans une variable de sortie qui passe un type de données XML.

HTH,

John

j'ai eu une situation similaire et résolu en utilisant une table de température à l'intérieur de la procédure, avec les mêmes champs étant retournés par la procédure stockée originale:

CREATE PROCEDURE mynewstoredprocedure
AS 
BEGIN

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

select field1, field2 from temptable

-- (mystoredprocedure returns field1, field2)

END

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 '''+@b+''' [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     ['+@c+'].dbo.invoicemain i inner join  ['+@c+'].dbo.ledgermain l on     l.ledgerid=i.ledgerid where (sp=0 or stocktype=''x'') and invoicetype=''DS'''

insérer dans @t exec [master].dbo.sp_executesql @q fetch next de @data @c@b fin fermer @data désallouer @data sélectionnez * à partir de @T retourner fin

voici un exemple de SP qui renvoie à la fois une table et une valeur de retour. Je ne sais pas si vous avez besoin du retour de la "valeur de retour" et je n'ai aucune idée de MATLAB et ce qu'il exige.

CREATE PROCEDURE test
AS 
BEGIN

    SELECT * FROM sys.databases

    RETURN 27
END

--Use this to test
DECLARE @returnval int

EXEC @returnval = test 

SELECT @returnval