Jump to content

Stored Proc Help


kreplech
 Share

Recommended Posts

Hello All,I have a stored proc:CREATE PROCEDURE sp_getCurrentStatus @id intasSELECT TOP 1 status_description FROM thisTableWHERE (status_date IN (SELECT MAX(status_date) FROM thisTable GROUP BY id)) AND a.id = @idGOintended to receive any [id] and return its current status_description (current status is the one with max(status_date)).next i have a sp returning all data for id (except for the current status) - something like: CREATE PROCEDURE sp_getDataasselect id, name from otherTableGOhow to return the results of sp_getCurrentStatus to sp_getData so results are:ID, NAME, STATUS_DESCRIPTION for each row of data in otherTable all from one simple call to the DB?Thanks,M

Link to comment
Share on other sites

You should use an inner join, off the top of my head (you may need to fix syntax):select top 1 t1.id, t1.name, t2.status_descriptionfrom thisTable t1inner join otherTable t2 on t1.id = t2.idwhere (t1.status_date IN(SELECT MAX(t1.status_date)FROM t1GROUP BY t1.id)) AND t1.id = @id

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...