Skip to Content
0

Nested Stored Procedure - Passing Output

May 26, 2017 at 10:38 PM

169

avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
avatar image
Former Member May 28, 2017 at 03:59 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

1
Lars Breddemann
May 29, 2017 at 09:19 AM
1

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.

Share
10 |10000 characters needed characters left characters exceeded