To make an UPDATE automatically

[turingpattern]

Hi
I have a table with seven entries. They are pictures, each one of them for a day of the week. Now I have an application in jsp to be able to change the picture.
But what I want to do it is an automatic process so that they change without my intervention.
I have a SQLServer 7.0
I have begun to read on triggers and stored processes and am taking ideas.
The code is easy, some like that:

SELECT idfoto AS idactual FROM FOTOPORTADA WHERE visible=1 AND seccion = 1
IF (idactual=41) // Mon
UPDATE FOTOPORTADA SET visibleweb = 0 WHERE idfoto = 41
UPDATE FOTOPORTADA SET visibleweb = 1 WHERE idfoto = 42
IF (idactual=42) // Tue
...
[/color red]
What I do not know is how to use the parameter that I obtain from the first SELECT (idactual) in the rest of code, to pass it to it to the IF. Some idea?
Thank you very much

 

[johnwm]

If I understand you correctly you just want to select a different picture each day of the week. If that is the case try setting the idphoto to 1 through 7. You can then use a simple SELECT using the DATEPART function in SQL:
Select idphoto FROM FOTOPORTADA
WHERE idphoto = DATEPART(dw, GETDATE())

GetDate returns the current date, and DatePart uses dw to produce the day of the week

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[mbreti]

I don't know if you already solved this problem, but if the others will have the same problem:

If you are using a stored procedure, first you can declare a variable and than use set command to set the value of that variable and then use it in the following code:

DECLARE @idactual int

SET @idactual = SELECT idfoto FROM FOTOPORTADA WHERE visible=1 AND seccion = 1
IF (@idactual=41) // Mon
.
.
.