4

Iam quite new to functions in SQL and I would like to create a function to compare values in a MySQL table against previous and I am not sure how to do this.

For example (iId is the input value)

DECLARE pVal INT(20);
DECLARE val INT(20);

SELECT price INTO pVal FROM products WHERE Id=iId;

SELECT price FROM products;

IF price == pVal THEN
SET val = price;
END IF;

Thanks

aHunter
  • 3,490
  • 11
  • 39
  • 46
  • 2
    it's not really clear exactly what your issue is? – Leslie Mar 16 '10 at 14:48
  • 1
    How do you define previous? What do you want to find? All records with different price or all different prices that a product had (if so, what does the table products store?) – Unreason Mar 31 '10 at 11:52
  • Your code is invalid. Couldn't you do the same thing by SET val =pVal; You're iterating over the same products for the same price. – Brant Messenger Apr 01 '10 at 20:27

1 Answers1

1

I was not sure how to run a select query on a table and then return from that function return multiple values once they have been manipulated. Also I was unsure if you could run a SELECT query in a function that returns more than one row. The first answer is that you can not return an array of data or I think more than one row from a function. Therefore I think the best way to do this is to create a temporary table with the new dataset returned.

Example 


DROP TEMPORARY TABLE IF EXISTS employeeTemp;
   CREATE TEMPORARY TABLE employeeTemp AS
   SELECT id,start_date
    FROM employee;

Secondly the answer is yes you can run a SELECT query inside a function to return more than one row.

Sorry about this I am quite new to MySQL functions.

aHunter
  • 3,490
  • 11
  • 39
  • 46
  • 2
    can you explain, what it is that you want to do? Cause right now I would think triggers might be what you want. – sfossen Mar 26 '10 at 16:21