可以将带有if语句的查询作为参数发送到MySql存储的proc并返回其结果 [英] Can a query with an if statement be sent as a parameter to a MySql stored proc and its result returned

问题描述

大家好，

我正在将我的数据库从mssql迁移到mysql.我在我的应用程序前端(在C#中)有这样的查询:

如果存在(从table1中选择*)，然后
从表1中选择*;
其他
从表2中选择*
如果结束，

现在，该值不能作为MySqlCommand的命令文本传递.网上的所有教程都说if..else只能嵌入到mysql中存储的procs和函数中.但即使如此，if的条件部分仍无法正常工作.有人可以提出解决方案吗?

--AR

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写道:

但即使如此，条件部分如果无法正常工作

but even then the condition part for if is not working properly

您为SQL语法编写了不正确的if语句.相反，您需要:

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
	)

至少，这就是它在Microsoft SQL Server上的工作方式.对于MySQL而言可能有所不同.

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

在大多数情况下，这是一种不好的做法，但是您可以使用"prepared statement"来执行此操作.这个想法是，您将在C#的字符串中创建一些SQL，该字符串的行为类似于谓词(返回true或false).然后，您传递要由存储过程执行的SQL字符串，该存储过程知道该语句为true时返回什么，如果该语句为false则返回什么(但它事先不知道该语句是什么) ).在此处了解更多有关准备好的语句的信息.另一个名称是动态SQL"，但是在MSSQL和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.

要添加到我先前的答案中，您可以有一个存储过程并传递4个字符串做到这一点.其中两个将是if语句(如果第二个if语句只是一个else，则只有一个if语句)，而两个将是返回两个可能结果之一的语句.

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.

这篇关于可以将带有if语句的查询作为参数发送到MySql存储的proc并返回其结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！