CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Learthgz
Learthgz

Reputation: 133

OUTPUT Parameters of stored procedure returns only one row

I have two tables in SQL Server :

Table A

ID Num
11 tj55
4  tj40

Table B

ID NUM A_ID
3 se400 4
5 se500 4

I want to create a stored procedure which will return for each row in A all corresponding rows in B, I want to have this result

NumA NumB
tj40 se400
tj40 se500

Code:

ALTER PROCEDURE [dbo].[Proc] 
    @param nvarchar(MAX),
AS
BEGIN
    SELECT 
        aa.Num,
        bb.Num
    FROM 
        [dbo].[A]  as aa
    INNER JOIN 
        [dbo].[B] AS bb ON bb.A_ID = aa.ID 
    WHERE 
        aa.Num = @param
    ORDER BY 
        aa.Num
 END

This stored procedure works fine and returns exactly what I need but when I add the OUTPUT parameters (which I need)

 ALTER PROCEDURE [dbo].[Proc] 
    @param nvarchar(MAX),
    @RET1 varchar(MAX) OUTPUT,
    @RET2 nvarchar(MAX) OUTPUT
 AS
 BEGIN
    SELECT 
       @RET1= aa.Num,
       @RET2 = bb.Num
    FROM 
       [dbo].[A]  as aa
    INNER JOIN 
       [dbo].[B] AS bb ON bb.A_ID = aa.ID 
    WHERE 
       aa.Num = @param
    ORDER BY 
       aa.Num
END

I have only one row as result

NumA NumB
tj40 se400

Thank you very much

Upvotes: 0

Views: 2624

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

A stored procedure only returns scalar values, not rows. It will print the results of a query, but a user-defined inline table function is better:

CREATE FUNCTION [dbo].[ProcFunc] (
    @param nvarchar(MAX)
)
RETURNS table
AS
RETURN (SELECT aa.Num, bb.Num
        FROM [dbo].[A] aa INNER JOIN
             [dbo].[B] bb
             ON bb.A_ID = aa.ID 
        WHERE aa.Num = @param
        ORDER BY aa.Num
       );

You can then call it as:

select *
from dbo.ProcFunc('abc');

Upvotes: 1

Related Questions