updating date by stored procedure

Question

I have a problem!

My task is to count the age of books in my library database. After that call some books as too rare, some rare , and usual using value column.

My library table ( ... , age- date , value- date)

notice: "age" - is incorrect definition of a column, it would be better to say "year of publication". Actually my task is to find age!

So, I do this, and my value column does not change :(

create procedure foo 
as
  declare @bookdate date,
          @currentdate date,
          @diff int

  set @currentdate = GETDATE()

  select @bookdate =  age from books

  select @diff = DATEDIFF (yyyy , @bookdate , @currentdate )

Version #1:

  UPDATE books SET value = DATEADD(year,@diff, age)

Version #2:

  UPDATE books SET value = @diff

P.S. sorry for any mistakes I made, it is my first step in sql, programming at all, and asking for help in English!

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Dec 20 '12 at 21:21
Could you give sample data / expected results? I'm guessing that part of your problem may be that you are updating all rows in the table to have the same value. — drneel, Dec 20 '12 at 21:22

twoleggedhorse · Accepted Answer · 2012-12-20T21:42:58.053

To me it sounds like you want something like this (I'm assuming you're using SQL Server as you've used the GETDATE() function):

CREATE PROCEDURE foo

AS
BEGIN

    SELECT   *
            ,DATEDIFF(yyyy,age,GETDATE()) AS YearsSincePublication
            ,CASE   WHEN DATEDIFF(yyyy,age,GETDATE()) > 200 THEN 'Too rare'
                    WHEN DATEDIFF(yyyy,age,GETDATE()) > 100 THEN 'Rare'
                    ELSE 'Usual'
             END AS Value
    FROM    books

END

Working form the top:
* means all columns from all tables
The datediff is working out the number of years since the publication and the AS bit names the resulting column (gives it an alias).

The CASE Statement is a way to test statements (if a equals b, do c). The first statement checks to see iff the book is more than 200 years old and if so, writes 'Too rare', the second line checks for more than 100 years, otherwise it writes 'usual'. Again, the AS is used to label the column to Value.

Finally the table we want our data from is specified, Books.

To run the stored procedure once you have created it is simply:

EXEC foo

You can always change GETDATE() to CURRENT_TIMESTAMP() if you're not using sql server, but you'll have to change the case statement to match your sql vendor. — twoleggedhorse, Dec 20 '12 at 21:36
Yes,I am using Sql server(2008). twoleggedhorse understood task perfectly.Thanks to twoleggedhorse!! and thank everybody for help! Did not know about case statement.I will study it. It was really helpful. Best Regards, Ievgenii — Ievgenii, Dec 21 '12 at 14:17

updating date by stored procedure

1 Answers1