Skip to Content

Nested Stored Procedure - Passing Output

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 28, 2017 at 03:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • May 29, 2017 at 09:19 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded