Nested Stored Procedure - Passing Output

[BenedictV]

Hi,

I have a sample code here,(SP 12)

create procedure "VENMANBV"."InnerProc"(IN in_dummy NVARCHAR(10))
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER 
   READS SQL DATA AS
BEGIN
Select * from "DUMMY";
END
call "VENMANBV"."InnerProc"(null)
create procedure "VENMANBV"."OuterProc"(IN im_productid NVARCHAR(10))
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER 
   READS SQL DATA AS
BEGIN
call "VENMANBV"."InnerProc"(null);
END
call "VENMANBV"."OuterProc"(null)

When the InnerProc is called directly I am able to get the output. But nothing happens when calling the OuterProc. I know I could define a table type and pass the output. But is there a way to get the output without explicitly defining a table type?

I remember seeing a discussion around here about this, passing the output implicitly. But I was not able to find it. Any help is appreciated?

[DotEiserman · Accepted solution]

Hi Benedict,

I would try explicitly creating an OUT parameter and assigning that from within your procedure, that way your procedure is returning a value. Have you tried that as well?

Kind regards,

Dot

[BenedictV · Accepted solution]

Thanks Dorothy. OUT parameters do work. Just wanted to check if there was a way to by pass this.

[lbreddemann]

Dot is spot on here (yeah, yeah, pun here, haha ;). Implicit result sets of procedures cannot be caught in SQLScript.

Either use a table typed output parameter or check if a table function can provide you with the expected semantic.