Accessing stored proc multiple return values

Hi,
I have a problem. I have two stored procs. One I am building currently
(sp_load) and another that is already in the data warehouse and which I
have no control over (sp_log_event).
sp_log_event is for control logging. It accepts a process name
parameter. It outputs 3 return parameters by issuing the following
command:
SELECT
load_id,
last_succ_load_id,
datEventDate
FROM
ctl_event_log_header
WHERE
load_Id = @.intLoadId
I am no expert on this but as I understand it these are technically not
output parameters. If I create an Execute SQL Task in DTS I have the
option of setting these 3 return values to my global variables in my
package - which is easy enough and I am already doing this.
However my problem is that I now need to call this (sp_log_event) from
within the stored proc I am creating (sp_load). Something like EXEC
MY_SP @.processname
If the return was an output parameter i could simply do EXEC MY_SP
@.processname, @.loadid output
Also if it was just one return value I could do
EXEC @.loadid = (MY_SP @.processname)
But it is neither of these scenarios and I can't work out how I can get
access to these 3 returned values from the confines of my procedure.
load id is a primary key so the select will definitely only return one
record. how do i get access to the 3 return variables and assign them
to variables within my stored proc (sp_load)evs
BOL has very good examples how to use storerd procedure that has a few
OUTPUT parameters
BTW , it is really bad practice to use sp_ prefix to name stored
procedures, because in that way SQL Server is going to check for system
stored procedures first
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a problem. I have two stored procs. One I am building currently
> (sp_load) and another that is already in the data warehouse and which I
> have no control over (sp_log_event).
> sp_log_event is for control logging. It accepts a process name
> parameter. It outputs 3 return parameters by issuing the following
> command:
> SELECT
> load_id,
> last_succ_load_id,
> datEventDate
> FROM
> ctl_event_log_header
> WHERE
> load_Id = @.intLoadId
>
> I am no expert on this but as I understand it these are technically not
> output parameters. If I create an Execute SQL Task in DTS I have the
> option of setting these 3 return values to my global variables in my
> package - which is easy enough and I am already doing this.
> However my problem is that I now need to call this (sp_log_event) from
> within the stored proc I am creating (sp_load). Something like EXEC
> MY_SP @.processname
> If the return was an output parameter i could simply do EXEC MY_SP
> @.processname, @.loadid output
> Also if it was just one return value I could do
> EXEC @.loadid = (MY_SP @.processname)
> But it is neither of these scenarios and I can't work out how I can get
> access to these 3 returned values from the confines of my procedure.
> load id is a primary key so the select will definitely only return one
> record. how do i get access to the 3 return variables and assign them
> to variables within my stored proc (sp_load)
>|||Hi Uri,
Thanks for the reply. First of all, what is BOL? :)
Second of all - I am not actually naming my stored procs like that. I
just used that for simplicity. They are actually USP_CTL_xxx and
USP_ETL_xxx
Cheers though.
Uri Dimant wrote:
> evs
> BOL has very good examples how to use storerd procedure that has a few
> OUTPUT parameters
> BTW , it is really bad practice to use sp_ prefix to name stored
> procedures, because in that way SQL Server is going to check for system
> stored procedures first
>
>
> "evs" <evan.winstanley@.gmail.com> wrote in message
> news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...|||evs
BOL -Books On Line (tool suppliedb by MS with SQL Server)

> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
I was referencing to <(sp_log_event). from your previous post
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149052185.818089.227430@.h76g2000cwa.googlegroups.com...
> Hi Uri,
> Thanks for the reply. First of all, what is BOL? :)
> Second of all - I am not actually naming my stored procs like that. I
> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
> Cheers though.
>
> Uri Dimant wrote:
>
>|||On 30 May 2006 21:38:45 -0700, evs wrote:

>Hi,
>I have a problem. I have two stored procs. One I am building currently
>(sp_load) and another that is already in the data warehouse and which I
>have no control over (sp_log_event).
>sp_log_event is for control logging. It accepts a process name
>parameter. It outputs 3 return parameters by issuing the following
>command:
>SELECT
> load_id,
> last_succ_load_id,
> datEventDate
>FROM
> ctl_event_log_header
>WHERE
> load_Id = @.intLoadId
>
>I am no expert on this but as I understand it these are technically not
>output parameters. If I create an Execute SQL Task in DTS I have the
>option of setting these 3 return values to my global variables in my
>package - which is easy enough and I am already doing this.
>However my problem is that I now need to call this (sp_log_event) from
>within the stored proc I am creating (sp_load). Something like EXEC
>MY_SP @.processname
>If the return was an output parameter i could simply do EXEC MY_SP
>@.processname, @.loadid output
>Also if it was just one return value I could do
>EXEC @.loadid = (MY_SP @.processname)
>But it is neither of these scenarios and I can't work out how I can get
>access to these 3 returned values from the confines of my procedure.
>load id is a primary key so the select will definitely only return one
>record. how do i get access to the 3 return variables and assign them
>to variables within my stored proc (sp_load)
Hi evs,
CREATE TABLE #tmp ( load_id -- datatype
, last_succ_load_id -- datatype
, datEventDate -- datatype
);
INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
EXEC MY_SP @.processname;
SELECT load_id, last_succ_load_id, datEventDate
FROM #tmp;
DROP TABLE #tmp;
Hugo Kornelis, SQL Server MVP|||Hugo,
Thank you so much mate! Works perfectly. I was aware of temporary
tables I have just never used them before and didn't think of it as an
option. Thanks again.
Hugo Kornelis wrote:
> On 30 May 2006 21:38:45 -0700, evs wrote:
>
> Hi evs,
> CREATE TABLE #tmp ( load_id -- datatype
> , last_succ_load_id -- datatype
> , datEventDate -- datatype
> );
> INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
> EXEC MY_SP @.processname;
> SELECT load_id, last_succ_load_id, datEventDate
> FROM #tmp;
> DROP TABLE #tmp;
> --
> Hugo Kornelis, SQL Server MVP