Hi, See The Code Below, It is giving me an error and i wanted to be able to see how the SQL STRING is compiled. Basically i want to see the text of the SQLSTRING(highlighted in red at the end) variable and instead i get the total characters in within the string and not the text itself. Please help.
DELIMITER$$
DROP PROCEDURE IF EXISTS bluerive_db1.Result$$
CREATE PROCEDURE Result(In_Scenario_ID INT, In_Campaign_ID INT , In_Company_ID INT /, OUT X TEXT/)
BEGIN
Declare No_More_Info BOOLEAN DEFAULT FALSE;
Declare EvalName VARCHAR(20);
Declare EvalID INT;
Declare SQLSTRING VARCHAR(4000);
/SET In_Scenario_ID = 1, In_Campaign_ID = 1, In_Company_ID = 380;/
/* Select Only The Correct Names and ID’s From The Evaluator Table which Belong to the Scenario, Campaign and Company*/
DECLARE The_Cursor CURSOR FOR
SELECT Distinct Evaluator.Evaluator_Name, Evaluator.Evaluator_ID From Score_Card
Left Join Evaluator on Evaluator.Evaluator_ID = Score_Card.Evaluator_ID
Left Join Metric on Metric.Metric_ID = Score_Card.Metric_ID
Where Metric.Scenario_ID = In_Scenario_ID and Score_Card.Campaign_ID = In_Campaign_ID and Score_Card.Company_ID = In_Company_ID;
/Create a loop by using a cursor to create the correct amount of columns for the Evaluators/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET No_More_Info := TRUE;
/Write a string output/
SET SQLSTRING := 'Select A.Metric_Name as Metric_Name ';
OPEN The_Cursor;
The_Loop: LOOP
FETCH The_Cursor Into EvalName, EvalID;
SET SQLSTRING := SQLSTRING +
’ ,(Select Distinct Score_Value From Score_Card Where Campaign_ID = A.Campaign_ID
and Company_ID = A.Company_ID and Evaluator_ID = ’ + EvalID + ’ and Metric_ID = A.Metric_ID ) as ’ + EvalName;
IF No_More_Info THEN
CLOSE The_Cursor;
LEAVE The_Loop;
END IF;
END LOOP The_Loop;
SET SQLSTRING := SQLSTRING + ’ FROM (Select Distinct Score_Card.Metric_ID, Metric.Metric_Name, Campaign_ID, Company_ID From Score_Card
Inner Join Metric on Metric.Metric_ID = Score_Card.Metric_ID
Where Campaign_ID = ’ + In_Campaign_ID +’ and Company_ID = ’ + In_Company_ID + ’ and Metric.Scenario_ID = ’ + In_Scenario_ID +
’ Order by 1) as A ';
[COLOR=Red]SELECT SQLSTRING;[/COLOR]
END$$
DELIMITER ;
