Q:

Return multiple values in stored procedure from function - SQL Server

sp1
ALTER procedure [dbo].[sp1]            
as             
begin                        
DECLARE @od varchar(255)
EXEC @od =dbo.getFn

SELECT CMSA.A,CMSB.B, @OD
FROM CMSA INNER JOIN CMSB
GROUP BY CMSA.A, CMSB.B
end

fn1
ALTER FUNCTION [dbo].[getFn]()
returns varchar(255)
as 
BEGIN 
DECLARE @Desc1 varchar(200)
DECLARE @Desc2 varchar(200)
DECLARE c1  CURSOR FOR
SELECT CMSC.C
FROM   CMSC INNER JOIN CMSB INNER JOIN CMSC
WHERE CMSC.C = "HELLO" OR CMSC.C = "GOODBYE"
GROUP BY CMSC.C
Open c1
FETCH NEXT FROM c1 INTO @Desc1
set @Desc2=@Desc1 
CLOSE c1
deallocate c1
Return @Desc2
end

Just say the existing value for CMSC is HELLO and GOODBYE, and i want to display both values in stored procedure from the function. For instance,
CMSA.A, CMSB.B, HELLO
CMSA.A, CMSB.B, GOODBYE

But now, I'm only able to display a single output instead of both of the output as I mentioned above. How can I solve this?

A:

Function 
The function seems erroneous as the inner joins don't have a ON statement.
ALTER FUNCTION [dbo].[getFn]()
returns @table TABLE (value varchar(255))
as 
BEGIN 
    DECLARE @Desc1 varchar(200)
    DECLARE @Desc2 varchar(200)

    DECLARE c1  CURSOR FOR

    SELECT CMSC.C
    FROM   CMSC 
    INNER JOIN CMSB 
    INNER JOIN CMSC
    WHERE CMSC.C = "HELLO" OR CMSC.C = "GOODBYE"
    GROUP BY CMSC.C

    Open c1
    FETCH NEXT FROM c1 INTO @Desc1
    set @Desc2=@Desc1 
    INSERT INTO @table VALUES (@Desc2)
    CLOSE c1
    deallocate c1

    Return 
end

SP
Again the syntax of the SP is also wrong as the ON statement is missing in the INNER JOIN.
ALTER procedure [dbo].[sp1]            
as             
begin                        
DECLARE @od varchar(255)
EXEC @od =dbo.getFn

SELECT CMSA.A,CMSB.B, a.Value
FROM CMSA INNER JOIN CMSB
INNER JOIN (SELECT * FROM dbo.getFn()) a
GROUP BY CMSA.A, CMSB.B
end

It might be an INNER JOIN or a CROSS JOIN depending on whether you have a field to reference on

