存储过程代码分享

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

-- =============================================                       
-- Author     : lijun cao                
-- Create date: 29/8/11                        
-- =============================================                       
ALTER PROCEDURE [dbo].[uspSysMainMenuLD]                       
@PK_Mnu_Id INT, @RStatus varchar(10)='',@FIELDNAME VARCHAR(20)='', @FIELDVALUE VARCHAR(20) = '',@Sort varchar(30)='' 
AS                       
BEGIN                       
  DECLARE @SQL VARCHAR(1000)                       
                     
 IF @PK_Mnu_Id=0                  
  SET @SQL = 'select a.PK_Mnu_Id,a.RStatus,Isnull(a.PMNUId,0)PMNUId,a.MnuName,isnull(a.Description,'''') as Description,a.DOrder,isnull(a.MNULink,'''') as MNULink from SysMenu a           
 WHERE RStatus!=0 and PmnuId is null '
                                                       
     
IF @RStatus <> ''                 
 BEGIN                 
  SET @SQL = @SQL + 'AND RStatus='''+ @RStatus +''''                 
 END                     
    IF @FIELDNAME<>'' AND @FIELDVALUE<>''                    
    BEGIN                    
  SET @SQL = @SQL + 'AND ' + @FIELDNAME + ' LIKE ''' +'%' + @FIELDVALUE +'%'''   
               
    END                   
                 
    IF @PK_Mnu_Id<>0                   
    BEGIN                 
  SET @SQL = 'select a.PK_Mnu_Id,a.RStatus,Isnull(a.PMNUId,0)PMNUId,a.MnuName,isnull(a.Description,'''') as Description,isnull(a.MNULink,'''') as MNULink,a.DOrder from SysMenu a           
 WHERE a.RSTATUS=2 AND PmnuId is null AND PK_Mnu_Id='+CONVERT(VARCHAR(10),@PK_Mnu_Id)                  
    END                   
   
 SET @SQL=@SQL+' order by a.'+@sort                   
  exec (@SQL)                   
  PRINT(@SQL)                   
END

 

调用方法：

exec uspSysMainMenuLD 0,'','','','PK_Mnu_Id'