mysql的if 结果,可以将带有if语句的查询作为参数发送到MySql存储过程并返回其结果...

helo all,

i am migrating my db from mssql to mysql. i have queries like this in my applications front end (in C#):

if exists(select * from table1) then

select * from table1;

else

select * from table2

end if;

now this cant be passed as command text for MySqlCommand. tutorials all over net say that if..else can be embedded only in stored procs & functions in mysql. but even then the condition part for if is not working properly. can anyone suggest a solution??

--AR

解决方案AR wrote:but even then the condition part for if is not working properly

You are writing the if statement improperly for SQL syntax. Instead, you want:

IF EXISTS (SELECT 1 FROM Table1)

BEGIN

SELECT * FROM Table1

END

ELSE

BEGIN

SELECT * FROM Table2

END

Put that into a stored procedure. If you want everything to be in a single ad-hoc query (without a stored procedure), you could do something like this:

SELECT

Table1.Column1 AS FirstColumn,

Table1.Column9 AS SecondColumn

FROM Table1

WHERE EXISTS

(

SELECT 1 FROM Table1

)

UNION

SELECT

Table2.Column2 AS FirstColumn,

Table2.ColumnR as SecondColumn

FROM Table2

WHERE NOT EXISTS

(

SELECT 1 FROM Table1

)

At least, that''s how it works on Microsoft SQL Server. Might be different for MySQL.

In most cases, it would be bad practice, but you might be able to use "prepared statements" to do this. The idea is that you''d create a bit of SQL in a string in C# that acts like a predicate (returns true or false). Then, you''d pass that SQL string to be executed by a stored procedure which knows what to return if the statement is true and what to return if the statement is false (but it doesn''t know in advance what that statement is). Read more about prepared statements here. Another name for this is "dynamic SQL", but the methods to use them differ between MSSQL and MySQL.

To add to my previous answer, you could have a stored procedure and pass 4 strings to it. Two of them would be the if statements (or only one if statement if the second if statement is just an else), and two would be statements that return one of two possible results.