Page 1 of 1

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

Posted: Wed Aug 09, 2006 9:51 am
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?

Posted: Wed Aug 09, 2006 11:46 am
by anjanesh
Isnt the scope of m_username within the procedure itself since the procedure acts as a function returning only a value ?

Posted: Wed Aug 09, 2006 4:41 pm
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.

Posted: Wed Aug 09, 2006 4:56 pm
by RobertGonzalez
Are you talking about stored procedures?

Posted: Wed Aug 09, 2006 5:06 pm
by raghavan20
definitely

Posted: Thu Aug 10, 2006 12:02 am
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 ;$$

Posted: Thu Aug 10, 2006 1:47 am
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$$

Posted: Fri Aug 11, 2006 9:23 am
by raghavan20
WHERE
userName LIKE concat('%', m_username, '%')

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