set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_getData]
@name varchar(20),
@field varchar(1000),
@startdate varchar(20),
@enddate varchar(20),
@filter varchar(1000)
AS
declare @sql varchar(max),
@starttime datetime,
@endtime datetime,
@startyear int,
@endyear int,
@curyear varchar(20)
BEGIN
set @startyear=1950
set @endyear=2200
if(@field is null or @field='')
set @field='*'
if(@startdate='')
set @startdate=@enddate
if(@enddate='')
set @enddate=@startdate
if(len(@startdate)=4)
set @startdate=@startdate+'0101'
if(len(@enddate)=4)
set @enddate=@enddate+'1231'
if(len(@startdate)=6)
set @startdate=@startdate+'01'
if(len(@enddate)=6 and isnumeric(@enddate)=1)
set @endtime=dateadd(dd,-1,dateadd(mm,1,cast(@enddate+'01' as datetime)))
if(isdate(@startdate)>0)
begin
set @starttime = cast(@startdate as datetime)
set @startyear=year(@starttime)
end
if(isdate(@enddate)>0)
begin
set @endtime = cast(@enddate as datetime)
set @endyear=year(@endtime)
end
set @sql=''
declare cur CURSOR for
select right(name,4) from sysobjects where (xtype='U' or xtype='V') and name like @name+'%'
open cur
fetch next from cur into @curyear
while @@Fetch_Status=0
begin
if(isnumeric(@curyear)>0 and cast(@curyear as int)>=@startyear and cast(@curyear as int)<=@endyear)
set @sql=@sql+'select * from '+@name+@curyear+' union '
fetch next from cur into @curyear
end
close cur;
deallocate cur;
if(@sql<>'')
begin
set @sql='select '+@field+' from ('+left(@sql,len(@sql)-6)+') a where '
if exists(select * from sys.syscolumns where id=object_id(@name) and name='ny')
set @sql=@sql+'ny>='''+left(@startdate,6)+''' and ny<'''+left(@enddate,6)+''''
else
set @sql=@sql+'rq>=cast('''+@startdate+''' as datetime) and rq<=cast('''+@enddate+''' as datetime)'
if(@filter is not null and @filter<>'')
set @sql=@sql+' and '+@filter
exec(@sql)
end
END这是我的存储过程,求大神帮我注释,谢谢,每句都是做什么的?
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_getData]
@name varchar(20),
@field varchar(1000),
@startdate varchar(20),
@enddate varchar(20),
@filter varchar(1000)
AS
declare @sql varchar(max),
@starttime datetime,
@endtime datetime,
@startyear int,
@endyear int,
@curyear varchar(20)
BEGIN
set @startyear=1950
set @endyear=2200
if(@field is null or @field='')
set @field='*'
if(@startdate='')
set @startdate=@enddate
if(@enddate='')
set @enddate=@startdate
if(len(@startdate)=4)
set @startdate=@startdate+'0101'
if(len(@enddate)=4)
set @enddate=@enddate+'1231'
if(len(@startdate)=6)
set @startdate=@startdate+'01'
if(len(@enddate)=6 and isnumeric(@enddate)=1)
set @endtime=dateadd(dd,-1,dateadd(mm,1,cast(@enddate+'01' as datetime)))
if(isdate(@startdate)>0)
begin
set @starttime = cast(@startdate as datetime)
set @startyear=year(@starttime)
end
if(isdate(@enddate)>0)
begin
set @endtime = cast(@enddate as datetime)
set @endyear=year(@endtime)
end
set @sql=''
declare cur CURSOR for
select right(name,4) from sysobjects where (xtype='U' or xtype='V') and name like @name+'%'
open cur
fetch next from cur into @curyear
while @@Fetch_Status=0
begin
if(isnumeric(@curyear)>0 and cast(@curyear as int)>=@startyear and cast(@curyear as int)<=@endyear)
set @sql=@sql+'select * from '+@name+@curyear+' union '
fetch next from cur into @curyear
end
close cur;
deallocate cur;
if(@sql<>'')
begin
set @sql='select '+@field+' from ('+left(@sql,len(@sql)-6)+') a where '
if exists(select * from sys.syscolumns where id=object_id(@name) and name='ny')
set @sql=@sql+'ny>='''+left(@startdate,6)+''' and ny<'''+left(@enddate,6)+''''
else
set @sql=@sql+'rq>=cast('''+@startdate+''' as datetime) and rq<=cast('''+@enddate+''' as datetime)'
if(@filter is not null and @filter<>'')
set @sql=@sql+' and '+@filter
exec(@sql)
end
END这是我的存储过程,求大神帮我注释,谢谢,每句都是做什么的?
解决方案 »
- 请问如何将ACCESS中A表中的列A,列B,列C的数据合并到列A里面?
- 如何通过串口发送一个给定的小数给单片机?
- 那位能帮我写一个尝试连接数据库的代码?
- MDI窗体问题.在显示子窗体时,主窗体的控件会显示在子窗体之上.
- 怎么样判断一个控件是自动生成的还是手动添加的组件
- 无窗口程序怎么使用clientsocket啊?能否给个例子?
- 阅读器输入
- -------Paradox7支持回滚吗?-------
- ListView的背景怎么改变?要求显示的内容为透明的?
- 为什么我自己的DLL文件在应用中为什么说是地址错误但是结果却又正确
- 在双击了“frxReport1”后,接下来要怎么做?
- 对Adodataset.Recordset直接赋值报错:Field '' has no dataset
不懂游标的不如先百度一下先有个概念,那一句不太懂的可以指出来,全部注释恐怕没人帮的上忙
前面的是定义变量,然后是用If来判断数据,定义游标,执行游标,然后关闭游标,设置SQL语句,执行。