?
Solved

How can I make a conditional where in a function or procedure using MySQL

Posted on 2011-03-03
1
Medium Priority
?
325 Views
Last Modified: 2012-05-11
Hello, I need a function or a store procedure that recieves a parameter install, uninstall or all so it make the right select query.

So far I've got this, and it doesn't work. Help please. Thanks.
BEGIN
	DECLARE vTQ INTEGER(11);

	IF(vAssType = 'all',
		SELECT
			COUNT(*) INTO vTQ
		FROM
			assignment
	    WHERE assignment.AssDate >= vDateFrom
    	AND assignment.AssDate <= vDateTo
	,SELECT
			COUNT(*) INTO vTQ
		FROM
			assignment
	    WHERE assignment.AssDate >= vDateFrom
    	AND assignment.AssDate <= vDateTo
        AND assignment.AssType = vAssType
	)

  RETURN vTQ;
END

Open in new window

0
Comment
Question by:IvanGarcete
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Accepted Solution

by:
mwiercin earned 2000 total points
ID: 35030148
Can you be more specific on how do you want to pass these parameters? Are you looking on two separate flags or a single string parameter?

Hope this will help:
DELIMITER ||
CREATE PROCEDURE test_procedure(_my_param int) 
BEGIN
   IF(_my_param = 1) THEN 
     SELECT "first option" ;
   ELSEIF (_my_param = 2) THEN
     SELECT "second option" ;
    ELSE 
      SELECT "completely different"; 
    END IF; 
END;
||
DELIMITER ; 

Open in new window


And the output:

mysql> call test_procedure(3);
+----------------------+
| completely different |
+----------------------+
| completely different | 
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call test_procedure(2);
+---------------+
| second option |
+---------------+
| second option | 
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call test_procedure(1);
+--------------+
| first option |
+--------------+
| first option | 
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Open in new window

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question