Stored Procedure Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Question

Author

Topic

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 11:38:03

Thanks in advance for any advice.

Problem:

I need to run a Stored Proc that uses a select statement to bring back a recordset and then I need to use that recordset in another another query to get the final result to display on the screen.

Normally, if I were doing a web page, I'd bring back two Recordsets and on the client side determine what to display.

I can't do that in this case. I need to do it all on the server side in a Stored Proc.

Can I create a recordset or two and end up with the same results as a web page?

Regards
Tim Sweet

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-10 : 11:39:43

Can you not do it in one query within the stored proc?

Mark

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 11:46:42

That's what I'd like to do. Not sure how that would look.

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 13:09:50

I've most of it working.

Question I have a recordset that has to date. I want to only inlcude the earlier date.

Any SQL Function I can use or a compare of some kind?

Thanks

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 13:10:41

Sorry that should read

I've most of it working.

Question I have a recordset that has two dates. I want to only inlcude the earlier date.

Any SQL Function I can use or a compare of some kind?

Thanks

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 13:25:02

I've got it working to a point.

I need to compare two dates and take the oldest one.

Currently I have:

dbo.just_date_simple(InitialDate) as Date

This could return one date or two dates or move.

How would the syntex go for that portion?

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-10 : 13:39:07

You're confusing the heck out of me...follow the hint link below to get an (correct) answer very fast.

But how about

SELECT CASE WHEN date1>date2 THEN date2 ELSE date1 END FROM myTable99

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 14:01:26

Ok....let me try this.

I have a query (which can be part of a Stored Procedure)

Select tblSdep.SDep_Id, tblSdep.MOC_ID, tblSdep.abr_id, SDEPName, tblSdep.SDEPCategory, dbo.Just_Date_Simple(tblSdep.SdepInitialDate)as SDepInitialDate, dbo.Just_Date_Simple(tblSdep.SDEPCompletedDate) as SDepCompletedDate, tblSdep.CreditEarned, tblSdep.DocumentType,
tblMOC.MOC_Id, tblMOC.Field_Id, dbo.just_date_simple(tblMOC.StartYear)as StartYear

from tblSdep, tblMOC

Where tblSdep.abr_id = '10986' and tblMOC.abr_id = '10986'

This portion: dbo.just_date_simple(tblMOC.StartYear)as StartYear

Could pull one or more dates.

I only want the oldest date:

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-10 : 15:22:01

Tim - having the full code of your query doesn't help massively when we don't have any background or data. I'd guess there's probably a better solution at hand, but you're much more likely to get helpful advice if you follow Brett's link and post according to those guidelines.

Mark

timsweet
Starting Member

31 Posts

Posted - 2005-08-10 : 16:18:23

tblMyTable has 3 fields fldID, fldStartDate and fldClassName.

My query pulls two records from that table for the same individual with different StartDates for different Classes (5/5/2003) and (5/5/2005)

tblMyTable2 has fields fldID, fldCredits and fldCredits_Earned_Date

I want to find only the credits that were earned before the lastest STARTDATE

So I want the lastest StartDate from tblMyTable.

I've tried this Select Min(fldStartDate)
From tblMyTable
Where fldId = (the individuals id) and fldActive = 'active'

This gives me the 5/5/2003 date. Perfect.

I need to now use that date '5/5/2003' in a query against tblMyTable2 go get the credits before that date - all in one stored procedure.

Select fldCredits
from tblMYTable2
Where fldCredits_Earned_Date < '5/5/2003' the date from the first query

That's the best I can do with the information.

Thanks for at least attempting to figure out my question

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-10 : 18:15:02

Tim
The way I'm interpreting your requirement is that you want to return all credits amassed by a student prior to the start of their earliest class. With this assumption, the following will do this for all students. You can then obviously constrain this to a particular student:

SELECT
	mt2.fldID, 
	mt2.fldCredits
FROM
	tblMyTable2 AS mt2
	JOIN 
	(
		SELECT
			mt.fldID, 
			MIN(mt.fldStartDate) AS fldStartDate
		FROM
			dbo.tblMyTable AS mt
		WHERE
		 	mt.fldActive = 'active'  --Not sure about this as you don't list it as a column in this table
		GROUP BY
			mt.fldID
	) AS mt
		ON mt2.fldID = mt.fldID
			AND mt2.fldCredits_Earned_Date < mt.fldStartDate

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources