ASP. net2.0 calls the MySQL Stored Procedure

MySQL has been added to the stored procedure since MySQL 5.0, because it has not been around for a long time. Many people are asking how to establish the stored procedure in MySQL, next, I have created one as I understand. in ASP. the call method in net2.0 is similar to that in SQL, but there is little difference. The following is a paging stored procedure in MySQL. MySQL limite brings us great convenience. do not write too much code!

Delimiter
$;

 

Drop procedure if exists
'Mytest'. 'mypage' $

 

Create definer = 'root' @ 'localhost' procedure
'Mypage '(

Tablename varchar (100 ),

Fieldsname varchar (100 ),

Pageindex int,

Pagesize int,

Sortname varchar (500 ),

Strwhere varchar (500)

)

Begin

Declare fieldlist varchar (200 );

If fieldsname = ''| fieldsname = NULL then

Set fieldlist = '*';

Else

Set fieldlist = fieldsname;

End if;

If strwhere = ''| strwhere = NULL then

If sortname = ''| sortname = NULL then

Set @ strsql = Concat ('select', fieldlist, 'from', tablename, 'limit
', (Pageindex-1) * pagesize,', ', pagesize );

Else

Set @ strsql = Concat ('select', fieldlist, 'From
', Tablename, 'ORDER BY', sortname, 'limit
', (Pageindex-1) * pagesize,', ', pagesize );

End if;

Else

If sortname = ''| sortname = NULL then

Set
@ Strsql = Concat ('select', fieldlist, 'from', tablename, 'where', strwhere ,'
Limit ', (pageindex-1) * pagesize,', ', pagesize );

Else

Set
@ Strsql = Concat ('select', fieldlist, 'from', tablename, 'where', strwhere ,'
Order by ', sortname, 'limit', (pageindex-1) * pagesize,', ', pagesize );

End if;

End if;

Prepare stmt1 from @ strsql;

Execute stmt1;

Deallocate prepare stmt1;

End $

 

Delimiter; $

ASP. net2.0 call method. Next time .......