Stored Procedure use result of another Stored Procedure

[gazzippy]

I have a stored procedure that looks up a record in a lookup table to find the last student id used, adds 1 to it, saves the record into this lookup table, then a new record is added to another table using this new student id.

This works fine, however I have another stored procedure to lookup values in a table to use as defaults, these are set by the user and can be different depending on which year the user chooses, hence why I cannot have the defaults set within the table.

When this stored procedure is ran it shows results as an access crosstab query would, there will only ever be one row in the result, question... how can I use the result of this stored procedure to update the student record I have just added to the table?

Thanks in anticipation...

 

[gazzippy]

I have found a way around this by converting the stored procedure that was picking up the default values to a view, then inserting the record into the table using a select from the view.

All works fine.

 

[Databaseguy]

I try to stay away from views. They're run locally, return an entire recordset,and aren't compliled.

I don't think you can:

Select * from sp_yourproc.

However, I have had success with creating a function and inner joining to it.

I.E:

Select * from tblYourTabel
Inner Join on
fn_SomeFunction.ID = tblYourTbl.ID

 

[gazzippy]

Thanks,

Not too fussed in this instance about the whole recordset being returned as it will only ever select all records from the table.