Okay, I'm only looking for a work around now. Could some one guide me?
EDIT:
Okay, So apparantly there's no way to get a function to call a dynamic procedure in MySql.
But I really need to do this select statement dynamically!
SELECT Location FROM DemoTable WHERE No_of_Hospitals > AvgCity(No_of_Hospitals)
Or find a better way to deal with this problem. The parameters in the AvgCity() will be dynamic all the time. That's kind of compulsory. The parameter passing will be the name of a column from the DemoTable. I need an efficient way to handle the Rules from the Rule Table and the DemoTable as I've stated below. Its been 3 days now that i've been stuck on this. HELP!! :s
For reference, here's the old problem i had. And the flow of everything that i've got done so far-
Old Problem:
Could some one help me out with a good way to go about this?
I'm pretty new to MySql and I can't seem to find a simple answer for this anywhere.
I have two tables that I'm working with. The first table contains a lot of columns filled with Integer values such as:
No_of_Hotels No_of_Hospitals
The second table is a Rule Table which contains a rule name followed by the actual rule.
Eg:
ID RuleName Rule
1 Example No_of_Hospitals > AvgCity(No_of_Hospitals)
The function that I have right now is:
CREATE FUNCTION AvgCity(columnName text) RETURNS float(10)
DETERMINISTIC
BEGIN
DECLARE columnAvg float;
SELECT AVG(columnName)
INTO columnAvg
FROM DemoTable;
RETURN (columnAvg);
END
But every time I pass a value to the function parameter - which is the name of one of the columns that I want the average for, I get a 0 in return.
I figured that this is happening because the parameter being passed into the AVG() Function is being passed as a string and not a column name.
So that's AVG("No_of_Hospitals") instead of AVG(No_of_Hospitals) which also returns 0.
How do I fix this and get a result?
I'll be executing the whole thing after the result with:
SELECT @Q:= CONCAT('SELECT Location FROM DemoTable WHERE ', Rules.Rule) FROM
Rules WHERE ID=1;
PREPARE stq FROM @Q;
EXECUTE stq;
DEALLOCATE PREPARE stq;
It all goes in a SELECT statement so, I do need the AvgCity() to be a Function and NOT a Procedure.
UPDATE 1:
So I decided to put a procedure inside the function.
What i have now is this function:
CREATE FUNCTION AvgCity(colName text) RETURNS float(10)
DETERMINISTIC
BEGIN
DECLARE colAvg float;
CALL AvgCityProcedure(colName,colAvg);
RETURN (colAvg);
END
And this Procedure:
CREATE PROCEDURE AvgCityProcedure(
IN colName VARCHAR(100),
OUT colAvg FLOAT)
BEGIN
SET @c1 = 0.0;
SET @M:= colName;
SET @QUERY:= CONCAT('SELECT AVG(',@M,') INTO @C1 FROM DemoTable');
PREPARE stmt FROM @QUERY;
EXECUTE stmt;
SET colAvg:=@C1;
SELECT colAvg;
END
UPDATE 2: I got the procedure working!
Followed this really nice example:
My SQL Dynamic query execute and get ouput into a variable in stored procedure
But i can't seem to manage to insert any value inside colAvg.
I tried a lot of combinations with the @QUERY, but i can't find the right one.
I keep ending up with ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
But if i remove the INTO colAvg part and just do
SELECT @QUERY:= CONCAT('SELECT AVG(',@M,') FROM DemoTable');
It totally prints the answer to screen. I need to return that to colAvg.
What am i doing wrong?
Alright! I got the Procedure working properly!
I got another problem now.
I get an error while returning values from my function to the procedure.
It says dynamic sql not allowed inside the function.
But i used the dynamic sql inside my procedure :S
Whats happening here?!
What I need it to look like in the end is:
SELECT Location FROM DemoTable WHERE No_of_Hospitals > AvgCity(No_of_Hospitals)
Could someone please set up a small bounty on this? I haven't got a single answer in more than a week now. :(
