how to write this kind of query in procedure?[solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

how to write this kind of query in procedure?[solved]

Post by raghavan20 »

let us say there is a mysql variable declared in procedure:

Code: Select all

m_username varchar(50) default null;
now i want to run a select query with like comparison...i cannot write like this because anything inside single quote is assumed as string.

Code: Select all

select
*
from
users
where
username = '%m_username%' ##now the problem is, it will search for the string, m_username instead of using the value in it.
how can we tell mysql to use the value inside it or other ways make mysql to understand it as variable?
Last edited by raghavan20 on Fri Aug 11, 2006 9:24 am, edited 1 time in total.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Isnt the scope of m_username within the procedure itself since the procedure acts as a function returning only a value ?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

what i posted is a snippet from the original procedure itself. assume m_username is a declared procedure variable and it holds some value which has to be compared.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Are you talking about stored procedures?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

definitely
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Here are two that I have put together...

Code: Select all

DELIMITER $$;

DROP PROCEDURE IF EXISTS `ebsl_main`.`proc_insert_admin`$$

CREATE PROCEDURE `proc_insert_admin`()
BEGIN
declare new_id int;
declare n int;
-- This gets the highest admin_id
select ebsl_admins.admin_id into n from `ebsl_main`.`ebsl_admins` order by ebsl_admins.admin_id DESC LIMIT 0, 1;
if n > 0 then
	set new_id = n + 1;
	select new_id;
else
	select 'It not wook';
end if;
END$$

DELIMITER ;$$

Code: Select all

DELIMITER $$;

DROP PROCEDURE IF EXISTS `ebsl_main`.`proc_select_sitedata`$$

CREATE PROCEDURE `proc_select_sitedata`(IN t_id INT)
BEGIN
  DECLARE team_count INT;
  SELECT COUNT(*) INTO team_count FROM ebsl_teams;
  SET team_count = team_count + 1;

  IF t_id > 0 AND t_id < team_count THEN
 	  SELECT ebsl_divisions.division_name, ebsl_teams.team_name, ebsl_teams.team_address,
	    ebsl_teams.team_city, ebsl_teams.team_state, ebsl_teams.team_zipcode,
	    ebsl_teams.team_phone, ebsl_teams.team_fax, ebsl_teams.team_cookie_domain,
	    ebsl_teams.team_cookie_name, ebsl_teams.team_cookie_path, ebsl_teams.team_cookie_secure,
	    ebsl_teams.team_server_name, ebsl_teams.team_server_port, ebsl_teams.team_session_length
	  FROM ebsl_teams
	  JOIN ebsl_divisions ON ebsl_teams.team_division = ebsl_divisions.division_id
	  WHERE ebsl_teams.team_id = t_id;
  ELSE
    SELECT team_count;
  END IF;

END$$

DELIMITER ;$$
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I do not understand the use of the procedures you have written with my question but I appreciate your intention to help me. Let me give me you a proper example so that you can start from there.

this sample procedure takes in an username, searches for number of entries matching the pattern of the username.

Code: Select all

delimiter $$
create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int  )
	deterministic
begin
	if length(m_username) = 0 then
		set m_username = 'jason' #set temporary name if empty
	end if

	set m_username_matched = 0;  #finds number of matches for the given username

	select
		count(*) into m_username_matched #store into the variable to be returned
	from
		sampleDB.sampleTable
	where
		userName like '%m_username%' #this is the statement that does not work#
	

end$$
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

WHERE
userName LIKE concat('%', m_username, '%')

is the solution which I came to know from my friend.
Post Reply