CODEWITHSUNDEEP
mysql
ConductedClever
ConductedClever

Reputation: 4305

How to prevent select statement to return output

In a query I have multiple lines of statements like the one bellow:

set @query = '';
select @query := concat(@query, ' union (select \'',bi.param2,'\' as tableName, ',bi.param3,' id, ', bi.param4, ' text from ', bi.param2,' where ',if(TRIM(bi.param5) is null, '1=1', bi.param5),')')
from itmanagement.BasicInfo bi where bi.param2 != '';
set @query := concat(SUBSTR(@query, 7, LENGTH(@query)));
set @wholeQuery := concat('select pe.*, bi.title label
, (case when bi.param1 = \'textfield\' then pe.ElemValue else od.text end) text
 from PRC_PcdElements pe join BasicInfo bi on pe.ElemType = bi.InfoID join 
 (',@query,")
 od on bi.param2 = od.tableName and pe.ElemValue = od.id 
 where bi.TypeID = 3 and pe.ProcedureID = '2';");
prepare stmt1 from @wholeQuery ;
execute stmt1 ;

This query returns two columns. One for the first select statement, which is just an assigning statement, and the output of exec statement which is the desired one.

I want to prevent first select statement to print out a table to the output or replace it with a better command if available.

Upvotes: 2

Views: 87

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 77073

You can create a temporary table and insert-select the desired result into it. Also, you can select your query into a cursor, iterate it and build up the variable you need.

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can try the following solution, using the SELECT ... INTO syntax:

SET @query = '';
SELECT CONCAT(@query, ' UNION(SELECT \'',bi.param2,'\' AS tableName, ',bi.param3,' id, ', bi.param4, ' text FROM ', bi.param2,' WHERE ', IF(TRIM(bi.param5) IS NULL, '1=1', bi.param5),')') INTO @query
FROM itmanagement.BasicInfo bi WHERE bi.param2 != '' LIMIT 1;
SET @query := CONCAT(SUBSTR(@query, 7, LENGTH(@query)));
SET @wholeQuery := CONCAT('SELECT pe.*, bi.title label
 , (CASE WHEN bi.param1 = \'textfield\' THEN pe.ElemValue ELSE od.text END) text
 FROM PRC_PcdElements pe JOIN BasicInfo bi ON pe.ElemType = bi.InfoID JOIN 
 (',@query,")
 od ON bi.param2 = od.tableName AND pe.ElemValue = od.id 
WHERE bi.TypeID = 3 AND pe.ProcedureID = '2';");
PREPARE stmt1 FROM @wholeQuery;
EXECUTE stmt1;

You can set the @query variable with a statement like this (without output):

SELECT column_name INTO @query FROM table_name WHERE condition = true LIMIT 1

Upvotes: 1

Related Questions