MYSQL Procure for Executing Multiple queries in single shot
Mysql Procedure produces more than one result sets - This is the sample for those who are searching for the "sample procedure in mysql" , which can be executed to get more than one result set. this can be linked to a data sources in vb.net or asp.net
In this code snip bit, i tried to explain how we can create the procedure in mysql that can be linked to the datasources of dropdownlist, listbox, gridview etc.,
In many of the samples provided, we dont find the sample for using mysql procedure along with .net, since we people continuosly make use of .net with sql server alone,
DELIMITER $$
CREATE PROCEDURE `tQueries`(Inqry varchar(50000))
begin
declare qry varchar(50000) default 0;
while instr(Inqry,';') > 0 do
set @qry = substring(Inqry,1,instr(Inqry,';'));
prepare stmt from @qry;
execute stmt;
if instr(Inqry,';') < length(Inqry) then
set Inqry = substring(Inqry,instr(Inqry,';')+1);
elseif instr(Inqry,';') = length(Inqry) then
set Inqry = '';
end if;
end while;
end $$
The above given code is a procedure, which is used to dissemble the given queries into single query, here i have used semicolon as a separator for separating the queries, the above procedure can be called or executed as follows
call TQueries("select 100*1;select 100*2;select 100*3;");
which produces, three different result sets, we can also make use of insert statement, update or delete statement to be executed in single shot like
call TQueries("
drop table if exists emp;
create table emp(eno int, ename varchar(10));
insert into emp values(1,'Bala');
insert into emp values(2,'Priya');
insert into emp values(4,'Tamil');
select * from emp;
update emp set eno = 3 where eno=1;
select * from emp;
delete from emp where eno='4';
select * from emp; ");
Any one find this helpful or Any one has any comments about this or Any one is using .net with MYSQL...?
Your valuable comments are welcome...