June 27, 2012

How to Set Default Limit in Store Procedure in SQL

This is stored procedure which show you rows return as per mention in stored procedure.

 CREATE PROCEDURE get_categories(  
 IN p_action_flag VARCHAR(50),  
 IN p_firstrow int,  
 IN p_lastrow int)  
 BEGIN  
 IF p_action_flag = 'all'  
 THEN  
 SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d  
 WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1'  
 AND gu.group_id IN (1, 7) GROUP BY u.user_id ORDER BY d.name LIMIT p_firstrow,p_lastrow;  
 ELSEIF p_action_flag = 'Most Viewed'  
 THEN  
 SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1' AND gu.group_id IN (1, 7)  
 GROUP BY u.user_id ORDER BY u.views DESC, d.name ASC LIMIT p_firstrow,p_lastrow ;  
 END IF  
 END