> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx

"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:
>
>> 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)
>> >
>

"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)
>

Hugo Kornelis 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