Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
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
Posted

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:
SQL
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:
SQL
	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.
 
Share this answer
 
v2
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.
 
Share this answer
 
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.
 
Share this answer
 
There are other ways to create an if statement in SQL. For example, you can use a SELECT CASE, and that will return a result depending on a condition. For example:
SQL
-- Not sure how you would capture the result of a prepared statement.
SELECT CASE WHEN EXISTS(SELECT 1 FROM Table1) THEN 1 ELSE 0 END

Or, you could insert into an intermediary table:
SQL
-- Inside prepared statement.
INSERT INTO BoolTable(BoolValue) VALUES
(
    SELECT CASE WHEN EXISTS(SELECT 1 FROM Table1)
        THEN 1 ELSE 0 END
)
-- Outside of prepared statement.
SELECT TOP 1 @IsTrue = BoolValue
FROM BoolTable ORDER BY BoolTableID DESC

A similar technique would be to generate the BoolTable row, then pass in the ID for the prepared statement to update (to make sure your code isn't conflicting with some other code that is also using the "BoolTable"):
SQL
UPDATE BoolTable SET BoolValue =
    (SELECT CASE WHEN EXISTS(SELECT 1 FROM Table1)
        THEN 1 ELSE 0 END)
WHERE BoolTableID = @BoolTableID

I'm not sure how they work in MySQL, but you could also use an output parameter (a parameter to gets passed to the caller):
SQL
SELECT @OutIsTrue =
    CASE WHEN EXISTS(SELECT 1 FROM Table1) THEN 1 ELSE 0 END

Anyway, those are just some ideas. I didn't check any of them (I play with MSSQL, not MySQL), but I'm sure one of them has to work.
 
Share this answer
 
AR@JR wrote:
i have queries like this in my applications front end


Why do you ? Your front end should have no SQL at all in it.


AR@JR wrote:
can anyone suggest a solution??


You can change your stored proc. For example, try if (select count(*) from table1) > 0 instead.
 
Share this answer
 
you can't send this stuff as a parameter to a stored proc, you have to write a stored proc that does the work for you. And you should have NO queries or SQL in your front end.
 
Share this answer
 
Two things here:

1 - surely you can see that you pushing 'answer' to ask more questions makes no sense ? Edit your post

2 - I told you ages ago, if you can't use exists, then use select count(*) from table and then compare that value with 0.
 
Share this answer
 
"this post is meant for Christian Graus alone. i would greatly appreciate if you could post an example rather than bossing around. what i need is a stored proc that executes sql statements with conditions that has exists or count or whatever inside it and the statements will be passed from my C#.net code during runtime. please try doing that from your machine, see if your ideas are practical before making suggestions (or giving orders). technology that we see today was developed through trial & error, so nothing wrong in trying once before broadcasting."

I've tried so often to make you understand that you should not post an answer that is really a question. The fact you cannot comprehend this, leaves me wondering what else I can hope to achieve.

I told you the answer, in MS SQL is the exec statement. A quick google tells me it's EXEC in MySQL, too. The Count(*) will definitely work. I've told you exactly what to do, I have written plenty of SQL and what I told you is correct.

No, technology is only developed by 'trial and error' in the third world where, apparently, people guess, then post here and hope someone will give them code they can copy and paste. Where I live, technology is developed by reading books, using google, and learning what is right, not by guessing.
 
Share this answer
 
you didnt get my point, i have several queries of this kind in my project & i want to send them as string parameters to stored procedures or functions that will return the result back. this procedure works fine with usual statements. but when if is put inside the query, everything goes wrong....
 
Share this answer
 
i have tried all combinations of parameter sending to a stored proc to get the result as aspdotnetdev said, but the prepared statements in my sql cannot execute an 'if exists' statement. i would be grateful if anybody would send me the code for a stored proc that does this kind of work.

thanks & regards, AR
 
Share this answer
 
this post is meant for Christian Graus alone. i would greatly appreciate if you could post an example rather than bossing around. what i need is a stored proc that executes sql statements with conditions that has exists or count or whatever inside it and the statements will be passed from my C#.net code during runtime. please try doing that from your machine, see if your ideas are practical before making suggestions (or giving orders). technology that we see today was developed through trial & error, so nothing wrong in trying once before broadcasting.

thanks & regards, AR
 
Share this answer
 
i, the humble 'third world'ian has managed to find the solution for my problem even with all my short comings. thanks to aspdotnetdev for all his help. as for christian graus, sir you would have made a great leader of ku klux klan. i may have doubts in the future which may sound 'third world'ish to you. so kindly ignore them & do not respond.
 
Share this answer
 
Wow, Ian, I apologise. Most people who ask questions and ignore the answers tend to be in the subcontinent. I am sorry, I apologise. Thanks for reminding me they make dumb people in the West, too.

Anyone who had half an idea how to write code could have solved it based on my answer. A lot of people fall over themselves to hand out code to people who can't write their own, I tend to try to help programmers only, by giving them enough info so they can write their own code.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900