Thread: SQL Job or stored procedure

Are there keys linking the two tables together? If so, which ones?

Might I suggest the following design...

Note: I'm not sure why you have a "Name" column in your RegDeadline table, when one already exists in your Registration table. I am also not sure whey you have an "Email" column in your RegDeadline table, as that column would be more appropriately associated with the entity stored in the Registration table. Not sure why you have a "Cost" column in your RegDeadline table, that would be more appropriately associated with the entity in your Registration table. The "AmountPaid" column would be more appropriate for the RegDeadline table.

Below is a best practice design, which adhere's to proper naming conventions and takes into consideration my notes above.

RegDeadline Table
-----------------
RegDeadlineID (PK)
RegistrationID (FK)
DeadlineDate
AmountPaid
InActive


Registration Table
-----------------
RegistrationID (PK)
Name
Address
Email
Cost
InActive

Based on the above database design structure you could execute the following query to get your results.

Code:

SELECT A.Name,
A.Address,
A.Email,
B.AmountPaid
FROM dbo.Registration As A
INNER JOIN dbo.RegDeadline As B On (A.RegistrationID = B.RegistrationID)
WHERE DateDiff(day, DeadlineDate, GetDate()) > 1
AND A.InActive = -1 (Get only Active Records)
AND B.InActive = -1 (Get only Active Records) 

You could do one of the following:

1) Create the sql as a stored procedure, then write a simple console application that connects to the database and executes the procedure. The setup the console app up as a scheduled task and schedule it to kickoff at 1:00 AM every day.

OR

2) Create a new SQL job and schedule it that way.

#2 is preferred as the least amount of steps possible reduces the risk of some chain in the process breaking.