存储过程知识点(实例)

1.带时间参数的存储过程

create proc dbo.aaaa
@type nchar(2),
@id varchar(15),
@BeginDate smalldatetime,
@EndDate smalldatetime
as
begin
 
 declare @sql nvarchar(4000),@GID int,@OfficeID varchar(20)
 select @sql='select id as 编号,name as 姓名, case sex when 1 then ''女'' else ''男'' end as 性别,
                      inputTime as 登记日间 from a# where deleted=0'

 select @GID=gid from  b#
 select @OID=oid from b#
 if(@type='a')
  select @sql=@sql+N' and id='''+@id+'''' 
 else
   select @sql=@sql+' and OID='''+@OID+''' and GID='+cast( @GID as varchar(10)) ;
 select  @sql=@sql+' and inputTime between  @BeginDate and @EndDate order by ID'
 print @Sql

 exec sp_Executesql @Sql,N'@BeginDate smalldatetime,@EndDate malldatetime',@BeginDate,@EndDate

end

2.带OUTPUT的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create proc dbo.GetList
 @Id int,  
 @NumRows int output
AS
BEGIN  
SET NOCOUNT ON;  

declare @sqlCount nvarchar(4000)    
set @sqlCount='select @NumRows=count(*) from A'
exec sp_executesql @sqlCount,N '@NumRows int output',@NumRows output; 
 END