Using a SP inside another SP 
Author Message
 Using a SP inside another SP

Hello,  I am trying to compile the following code but the syntax is
incorrect when I try to assign a cursor to the results of the second SP
(MissingSheets) call.

Please Advise,
Dan

ALTER PROCEDURE NotifyDeliquentTimeSheets
AS



DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
 SELECT AssigneeID
 FROM AssigneeProperty
 WHERE StartDate IS NOT NULL
/*
 Get all users that have at least one timesheet
*/
OPEN AllUsers



BEGIN


 /*
  Loop to see if there are any missing timesheets
 */
 DECLARE MissingSheets CURSOR FOR

 OPEN MissingSheets


  PRINT '         <<All Sheets are uptodate>>'



 BEGIN
 END
 CLOSE MissingSheets
 DEALLOCATE MissingSheets


END

CLOSE AllUsers
DEALLOCATE AllUsers



Tue, 17 Jan 2006 16:08:56 GMT
 Using a SP inside another SP

You'll have to use some sort of SELECT statement for the
CURSOR declaration. You could change MissingSheets from a
procedure to a table function, then change the the CURSOR
declaration to call the function:

  DECLARE MissingSheets CURSOR FOR

I hope this helps. Good luck!

Quote:
>-----Original Message-----
>Hello,  I am trying to compile the following code but the
syntax is
>incorrect when I try to assign a cursor to the results of
the second SP
>(MissingSheets) call.

>Please Advise,
>Dan

>ALTER PROCEDURE NotifyDeliquentTimeSheets
>AS



>DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
> SELECT AssigneeID
> FROM AssigneeProperty
> WHERE StartDate IS NOT NULL
>/*
> Get all users that have at least one timesheet
>*/
>OPEN AllUsers



>BEGIN


> /*
>  Loop to see if there are any missing timesheets
> */
> DECLARE MissingSheets CURSOR FOR

> OPEN MissingSheets


>  PRINT '         <<All Sheets are uptodate>>'



> BEGIN
> END
> CLOSE MissingSheets
> DEALLOCATE MissingSheets


>END

>CLOSE AllUsers
>DEALLOCATE AllUsers



Tue, 17 Jan 2006 16:59:08 GMT
 Using a SP inside another SP
You have to use a SELECT for CURSOR declaration. You could
change GetNullTimeSheets from a procedure to a table
function, the change the CURSOR declaration to call the
function:

  DECLARE MissingSheets CURSOR FOR

I hope this helps. Good luck!

--Angel
--------------------------------
Every time I lose my mind,
I wonder if it's really worth finding.

Quote:
>-----Original Message-----
>Hello,  I am trying to compile the following code but the
syntax is
>incorrect when I try to assign a cursor to the results of
the second SP
>(MissingSheets) call.

>Please Advise,
>Dan

>ALTER PROCEDURE NotifyDeliquentTimeSheets
>AS



>DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
> SELECT AssigneeID
> FROM AssigneeProperty
> WHERE StartDate IS NOT NULL
>/*
> Get all users that have at least one timesheet
>*/
>OPEN AllUsers



>BEGIN


> /*
>  Loop to see if there are any missing timesheets
> */
> DECLARE MissingSheets CURSOR FOR

> OPEN MissingSheets


>  PRINT '         <<All Sheets are uptodate>>'



> BEGIN
> END
> CLOSE MissingSheets
> DEALLOCATE MissingSheets


>END

>CLOSE AllUsers
>DEALLOCATE AllUsers

>.



Tue, 17 Jan 2006 17:01:19 GMT
 Using a SP inside another SP
Angel,

The syntax you provided does not appear to be correct.  I have tried in both
SQL 7 and 2000

Thanks,
Dan

The syntax you supplied does not appear to work for

Quote:
> You have to use a SELECT for CURSOR declaration. You could
> change GetNullTimeSheets from a procedure to a table
> function, the change the CURSOR declaration to call the
> function:

>   DECLARE MissingSheets CURSOR FOR

> I hope this helps. Good luck!

> --Angel
> --------------------------------
> Every time I lose my mind,
> I wonder if it's really worth finding.

> >-----Original Message-----
> >Hello,  I am trying to compile the following code but the
> syntax is
> >incorrect when I try to assign a cursor to the results of
> the second SP
> >(MissingSheets) call.

> >Please Advise,
> >Dan

> >ALTER PROCEDURE NotifyDeliquentTimeSheets
> >AS



> >DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
> > SELECT AssigneeID
> > FROM AssigneeProperty
> > WHERE StartDate IS NOT NULL
> >/*
> > Get all users that have at least one timesheet
> >*/
> >OPEN AllUsers



> >BEGIN


> > /*
> >  Loop to see if there are any missing timesheets
> > */
> > DECLARE MissingSheets CURSOR FOR

> > OPEN MissingSheets


> >  PRINT '         <<All Sheets are uptodate>>'



> > BEGIN
> > END
> > CLOSE MissingSheets
> > DEALLOCATE MissingSheets


> >END

> >CLOSE AllUsers
> >DEALLOCATE AllUsers

> >.



Tue, 17 Jan 2006 17:12:51 GMT
 Using a SP inside another SP
Have you changed the GetNullTimeSheets procedure into a
function that returns a table? If not, post the code for
it and we can help.

Then in Query Analyzer, try calling the function in a
SELECT statement, passing it a valid AssigneeID:
  SELECT * FROM GetNullTimeSheets(12345)

Only after these two steps are successful will you be able
to use the function in the SELECT for the CURSOR
declaration.

--Angel

Quote:
>-----Original Message-----
>Angel,

>The syntax you provided does not appear to be correct.  I
have tried in both
>SQL 7 and 2000

>Thanks,
>Dan

>The syntax you supplied does not appear to work for


>> You have to use a SELECT for CURSOR declaration. You
could
>> change GetNullTimeSheets from a procedure to a table
>> function, the change the CURSOR declaration to call the
>> function:

>>   DECLARE MissingSheets CURSOR FOR

>> I hope this helps. Good luck!

>> --Angel
>> --------------------------------
>> Every time I lose my mind,
>> I wonder if it's really worth finding.

>> >-----Original Message-----
>> >Hello,  I am trying to compile the following code but
the
>> syntax is
>> >incorrect when I try to assign a cursor to the results
of
>> the second SP
>> >(MissingSheets) call.

>> >Please Advise,
>> >Dan

>> >ALTER PROCEDURE NotifyDeliquentTimeSheets
>> >AS



>> >DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
>> > SELECT AssigneeID
>> > FROM AssigneeProperty
>> > WHERE StartDate IS NOT NULL
>> >/*
>> > Get all users that have at least one timesheet
>> >*/
>> >OPEN AllUsers



>> >BEGIN


>> > /*
>> >  Loop to see if there are any missing timesheets
>> > */
>> > DECLARE MissingSheets CURSOR FOR

>> > OPEN MissingSheets


>> >  PRINT '         <<All Sheets are uptodate>>'



>> > BEGIN
>> > END
>> > CLOSE MissingSheets
>> > DEALLOCATE MissingSheets


>> >END

>> >CLOSE AllUsers
>> >DEALLOCATE AllUsers

>> >.

>.



Tue, 17 Jan 2006 17:31:10 GMT
 Using a SP inside another SP
Angel,

I did not completely read your message and missed the changing the procedure
to a function.  Unfortunately the procedure GetNullTimeSheets is used in
another program and I cannot change it.  I found another solution posted a
couple of days ago that creates a temporary table that I might try.

Thanks,
Dan


Quote:
> Have you changed the GetNullTimeSheets procedure into a
> function that returns a table? If not, post the code for
> it and we can help.

> Then in Query Analyzer, try calling the function in a
> SELECT statement, passing it a valid AssigneeID:
>   SELECT * FROM GetNullTimeSheets(12345)

> Only after these two steps are successful will you be able
> to use the function in the SELECT for the CURSOR
> declaration.

> --Angel

> >-----Original Message-----
> >Angel,

> >The syntax you provided does not appear to be correct.  I
> have tried in both
> >SQL 7 and 2000

> >Thanks,
> >Dan

> >The syntax you supplied does not appear to work for


> >> You have to use a SELECT for CURSOR declaration. You
> could
> >> change GetNullTimeSheets from a procedure to a table
> >> function, the change the CURSOR declaration to call the
> >> function:

> >>   DECLARE MissingSheets CURSOR FOR

> >> I hope this helps. Good luck!

> >> --Angel
> >> --------------------------------
> >> Every time I lose my mind,
> >> I wonder if it's really worth finding.

> >> >-----Original Message-----
> >> >Hello,  I am trying to compile the following code but
> the
> >> syntax is
> >> >incorrect when I try to assign a cursor to the results
> of
> >> the second SP
> >> >(MissingSheets) call.

> >> >Please Advise,
> >> >Dan

> >> >ALTER PROCEDURE NotifyDeliquentTimeSheets
> >> >AS



> >> >DECLARE AllUsers  CURSOR FORWARD_ONLY FOR
> >> > SELECT AssigneeID
> >> > FROM AssigneeProperty
> >> > WHERE StartDate IS NOT NULL
> >> >/*
> >> > Get all users that have at least one timesheet
> >> >*/
> >> >OPEN AllUsers



> >> >BEGIN


> >> > /*
> >> >  Loop to see if there are any missing timesheets
> >> > */
> >> > DECLARE MissingSheets CURSOR FOR

> >> > OPEN MissingSheets


> >> >  PRINT '         <<All Sheets are uptodate>>'



> >> > BEGIN
> >> > END
> >> > CLOSE MissingSheets
> >> > DEALLOCATE MissingSheets


> >> >END

> >> >CLOSE AllUsers
> >> >DEALLOCATE AllUsers

> >> >.

> >.



Tue, 17 Jan 2006 17:57:38 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. How can I call a SP inside a SP without the owner name

2. Calling SP from inside another SP

3. Use a sp to Make a view with a sp inside

4. sp inside sp

5. calling SP inside SP

6. How Get Output Param from Inside SP, calling another SP

7. Check if parameter supplied to SP inside SP

8. SP:ExecContextHit,SP:CacheMiss,SP:CacheInsert

9. SP Question - Insert into table using generic insert SP

10. using a sp return value within another sp

11. using the outcome of a SP in another SP

12. using result of one SP in another SP


 
Powered by phpBB® Forum Software