SqlServer - 工作总结

循环处理清单

create table d_gclqd(
	qdbh varchar(20) primary key,
	sjqd varchar(20),
	jdjb int,
	htsl decimal(18,5)
)
insert into d_gclqd values('100',		'',			1,	0)
insert into d_gclqd values('100-1',		'100',		2,	0)
insert into d_gclqd values('100-1-1',	'100-1',	3,	111)
insert into d_gclqd values('100-1-2',	'100-1',	3,	112)
insert into d_gclqd values('100-2',		'100',		2,	0)
insert into d_gclqd values('100-2-1',	'100-2',	3,	121)
insert into d_gclqd values('100-3',		'100',		2,	103)
insert into d_gclqd values('200',		'',			1,	0)
insert into d_gclqd values('200-1',		'200', 		2,	0)
insert into d_gclqd values('200-1-1',	'200-1',	3,	211)
insert into d_gclqd values('200-2',		'200',		2,	0)
insert into d_gclqd values('200-2-1',	'200-2',	3,	221)

declare @jdjb  int
select @jdjb=max(jdjb) from d_gclqd

if @jdjb is null
	return
while @jdjb>0
	begin
		update d_gclqd set htsl = b.htsl from (
			select sjqd,sum(htsl) as htsl  from d_gclqd where jdjb=@jdjb group by sjqd
		)b where qdbh=b.sjqd
		set @jdjb=@jdjb-1
	end
select * from d_gclqd

清库语句

if exists(select * from sysobjects where name= 'sp_clear_log') 
	drop procedure sp_clear_log 
go

create procedure sp_clear_log 
	@db_name varchar(255)
with encryption as
begin 
	dump transaction @db_name with no_log  
	backup log @db_name with no_log  
	dbcc shrinkdatabase(@db_name) 
end 
go
--sp_clear_log qnmis

得到旬的函数

-- select dbo.getxunbydate('2012/10/11')  
-- select dbo.getxunbydate('2012.10.11')  
-- select dbo.getxunbydate('2012-10-11')  
  
alter function getxunbydate(@date varchar(10))  
    returns varchar(10)   
as  
begin  
    declare @result varchar(10)  
    declare @number int  
    set @number=day(@date)  
    if @number <= 10  
        begin  
            set @result='上旬'  
        end  
    else if @number>10 and @number<=20  
        begin  
            set @result='中旬'  
        end  
    else if @number>20 and @number<=31  
        begin  
            set @result='下旬'  
        end  
    return @result                
end