Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server 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
Posted 2-Feb-10 18:45pm
AR@JR634
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 10

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:
-- 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:
-- 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"):
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):
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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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....
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 9

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 12

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 13

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 11

"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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 404
1 Nirav Prabtani 268
2 _Amy 230
3 CHill60 170
4 Sergey Alexandrovich Kryukov 166
0 OriginalGriff 8,004
1 Sergey Alexandrovich Kryukov 7,017
2 Maciej Los 4,039
3 Peter Leow 3,738
4 CHill60 2,912


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 8 Feb 2010
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100