Date driven T-sql Stored Procedure

[thegiraffe]

A little out of my league.....

I want to write a trigger / stored procedure to run on a pre defined day of the month (ie. the first), every month.

I want it to run only once a month checking information from a sales table and setting up records to a pending table if my criteria is matched.

Complete beginner with this so any pointers and tips / scripts will help me no end (and save my ass!!)

Cheers Dave

Theres nothing worse than an idea when its the only one we have!

 

[Sammy145]

yes craete a stored procedure or DTS packge place your statement TEST it
PLACE IT IN SQL SERVER JOB which is in the SQL SERVER AGENT this is a SQL schedular.
set up the schedule for 30 days and whatever time Call your Stored procedure and VOILA

Sql server agent is a service inside enterprise manager make sure you startt it(yuo see a green arrow and also make sure it has login with domain rights if running on domain)

take a look at

http://www.SQLDTS.com

please ask extra if you gets stuck.

Sam

 

[thegiraffe]

Right, i know it can be done now, my only problem is I'm struggling to piece together a stored procedure, i need to walk before i can run i think.

Really really sorry, I just need an initial push then i think i should be cool.

All examples of stored procedures seem way complex, is there an initial skeleton someone can point me to. Dave

Theres nothing worse than an idea when its the only one we have!

 

[Sammy145]

What is Your table structure can you list the FIELDS

or tell me what you want

here is a sample


CREATE PROCEDURE dbo.sampleSP
SELECT * FROM Custoemrs
GO


what you need to do is goto Query anyliser choose northwind database in the drop down box

now copy and paste the statement

Now delete the code in query anyliser and run this

EXEC sampleSP

you see results

Now to do your query replace the select statement with one of your own

example say you want total sales per item

select productnumber,SUM(Price)
from Example
group by productnumber
This statement will give you total price made per item

sam

 

[Sammy145]

Sorry my spelling is not very good

please use this

CREATE PROCEDURE dbo.sampleSP
AS
SELECT * FROM Customers
GO

EXEC sampleSP

sam

 

[thegiraffe]

Cheers, I kind of have something working....

Done what you said, i can pull info through with different criteria and stuff, still not sure how to set this to be date based, and I'm still playing with statements to try and update stuff.

Still haven't got the big picture as to how to run and call these, and add variables into the cooking pot, but i guess i'll get to that when i need to.

Thankyou, Dave

Theres nothing worse than an idea when its the only one we have!