Minor help with SPs/Jobs - 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)

Minor help with SPs/Jobs

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-09 : 01:23:19

Hey gals and guys. I've got an asp page that executes a few checks on data retreived and then updates the record depending on certain values (like a dtae past another certain date.) It works great, except the problem lies that it only gets executed when the page is loaded. Regarding SPs and Jobs, I'm at the entry level so I might not know what I can and cannot do as why I'm here.

My Ultimate Goal:
To create a stored procedure that:
1) retreives a date value on every record on a table
2) retreive another value from another table (number of months)
3) add the number of months to the date
4) check the date vs todays date
5) if the new date is earlier than todays date, update a value in the record from which got the date value.
6) go to the next record

I want to have this SP run once a day thru a scheduled job, but I have so many questions and depending on their answers I can go any which way I wish/can.

The thing is, fine I know how to select records froom a table, but can I read the fields straight from the SP do the calculations then update, all thru that one SP? Or do I have to create a job that uses active-x scripting like vbscript to do these readings and calculation? I'm trying to look for some SP eamples online to get a head start so I can see what is possible and while I'm at it, I'm here to get real human input

I appreciate any answers or redirections to sites with great examples. Anything to fill up this vacant brain of mind!

Thanks.

- RoLY roLLs

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-09 : 05:45:12

-- Retrieve the number of months
DECLARE @NoMonths INT
SELECT @NoMonths = <expression> FROM OtherTable
-- (Sorry, you didn't give much information here)

-- Update the table conditionally
UPDATE MyTable
SET aValue = <unspecified expression>
WHERE DATEADD(mm, @NoMonths, DateInThisRow) < GETDATE() -- Update only when earlier than today

Fill in the missing expressions and wrap it in a stored procedure, it's a natural for a stored procedure.

Next, you'll need to schedule a once a day job to execute the stored procedure. Alternatively, you could avoid writing a SP and schedule the SQL code without wrapping it in CREATE PROCEDURE.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-09 : 12:35:48

See I knew it something simple. I just have been using simple sql statements before I'm more familiar with maintaining an sql server than writing sql statements themselves...heh, go figure. You solution is perfect, but I have just one question for future concerns. Now I know that to make set a field value to a variable I 'declare' the variable the use 'select <variable> = <sqlstatment>'. But what if I need 2, 3 let's say 5 values from the the same recordset being returned. Do I need to write that statement 5 times and just return the particular field for the variable being set?

Thanks.

- RoLY roLLs

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-09 : 12:54:10

Select @var1 = col1, @var2 = col2, @varN = coln

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-09 : 15:59:39

oooooo thanks!

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-11 : 16:01:49

Hey Sam, I have finally gotten around to implement the code u gave, and I realized that ur code only get's the months once, but the month varies for each record. for example, I provide a service. You as my client/customer purchase 2 different services service #1 for 2 months and service #2 for 4 months. here's how the tables are composed: Invoice table which has the invoice total invoice date, invoice number and such, then I have another table invoicedetail which has the invoice number it belongs to (the invoice pk) the description of service (once record for service #1, and another record for service #2), and the number of months for each respective service.

So here's the deal, we got 2 tables each related to the other. i want to be able to update the table invoicedetail if the invoice date + the invoicedetail months is ealier than today, your code is great, I just need it to work to get the correct values, and i'm sure it can be done in just one select statement similar to urs, just by nesting select statements, no? Sorry still learning t-sql little by little.

Thanks alot for your time. Got any questions feel free to ask.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-11 : 16:19:58

here's is what I came up with but I'm not sure if it can be done:

UPDATE classinvoicedetail
SET classinvoicedetailexpired = 1
WHERE DATEADD(mm, (classinvoicedetaillength), (select c.classinvoicedate from classinvoice c inner join classinvoicedetail cd on c.classinvoiceid = cd.classinvoiceid where cd.classinvoicedetailid = cd.classinvoicedetailid)) < GETDATE()

That's what i can come up with, but take not of the bold text, I KNOW that is wrong because i need to access the currect record beinf read at the update level, but i donot know how to reference the 'classinvoicedetail' table at the update. I tried something like;

UPDATE classinvoicedetail cid
SET classinvoicedetailexpired = 1
WHERE DATEADD(mm, (cid.classinvoicedetaillength), (select c.classinvoicedate from classinvoice c inner join classinvoicedetail cd on c.classinvoiceid = cd.classinvoiceid where cd.classinvoicedetailid = cid.classinvoicedetailid)) < GETDATE()

But I got errors saying incorrect syntax at cid. If i knew how to give that table on teh first line an alias, then maybe it will work, or maybe you have a better way or maybe it can't be done expect to cycle thru the records thru some sorta loop.

Thanks again and Happy Holidays to all.

- RoLY roLLs

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-11 : 23:35:20

UPDATE cid
SET cid.classinvoicedetailexpired = 1
FROM
classinvoice c
INNER JOIN classinvoice_detail cid ON c.classinvoiceid = cid.classinvoiceid
WHERE
DATEADD(mm,cid.classinvoicedetaillength,c.classinvoicedate) < GETDATE()

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-12 : 12:39:07

Thank you VERY much! Worked perfect! I wasn't ware of using FROM with the UPDATE statement. guess I gotta get me a good t-sql book. You know of any intermediate to advanced book? I don't want beginner's book. Thanks!

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 12:41:31

http://www.sqlteam.com/store.asp

Guru's guide books and Joe Celko's books are what I would recommend.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-12 : 12:42:43

TY very much Tara

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources