代码如下:
if exists(select * from sysobjects where name='prCreateSYSCWDate' and type='P')
Drop Proc prCreateSYSCWDate
go
Create Proc prCreateSYSCWDate
@StartDate DateTime,
@EndDate DateTime,
@CWDate varchar(10)
with Encryption
as
begin
declare @AccountNO varchar(10),@i int
set @i=0
WHILE convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2) <=convert(varchar(10),year(@EndDate))+right(100+month(@EndDate),2)
BEGIN
insert into sysCWDateTB select AccountNo=convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2),
StartDate=convert(varchar(10),year(DATEADD ( MM , @i-1, @StartDate )))+'-'+right(100+month(DATEADD ( MM , @i-1, @StartDate )),2)+'-'+right(100+convert(int,@CWDate),2),
EndDate=convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+'-'+right(100+month(DATEADD ( MM , @i, @StartDate )),2)+'-'+right(100+(convert(int,@CWDate)-1),2),
bNowDate=0,
bStorageAccount=0,
bCWAccount=0
where convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2) not in (select AccountNo from sysCWDateTB)
set @i=@i+1
end
end
go
能正常生成,但是双击该存储过程会提示错误:Encrypted object is not transferable,and script can not generated
但是检查语法又是正常的~~~
请大大来救下命!!!
本人SQL不太懂,请详细说下方法
if exists(select * from sysobjects where name='prCreateSYSCWDate' and type='P')
Drop Proc prCreateSYSCWDate
go
Create Proc prCreateSYSCWDate
@StartDate DateTime,
@EndDate DateTime,
@CWDate varchar(10)
with Encryption
as
begin
declare @AccountNO varchar(10),@i int
set @i=0
WHILE convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2) <=convert(varchar(10),year(@EndDate))+right(100+month(@EndDate),2)
BEGIN
insert into sysCWDateTB select AccountNo=convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2),
StartDate=convert(varchar(10),year(DATEADD ( MM , @i-1, @StartDate )))+'-'+right(100+month(DATEADD ( MM , @i-1, @StartDate )),2)+'-'+right(100+convert(int,@CWDate),2),
EndDate=convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+'-'+right(100+month(DATEADD ( MM , @i, @StartDate )),2)+'-'+right(100+(convert(int,@CWDate)-1),2),
bNowDate=0,
bStorageAccount=0,
bCWAccount=0
where convert(varchar(10),year(DATEADD ( MM , @i, @StartDate )))+right(100+month(DATEADD ( MM , @i, @StartDate )),2) not in (select AccountNo from sysCWDateTB)
set @i=@i+1
end
end
go
能正常生成,但是双击该存储过程会提示错误:Encrypted object is not transferable,and script can not generated
但是检查语法又是正常的~~~
请大大来救下命!!!
本人SQL不太懂,请详细说下方法
@StartDate DateTime,
@EndDate DateTime,
@CWDate varchar(10)
with Encryption 这个with语句是加密存储过程,加密后双击就打不开了!但是可以调用
你如果要打开,就把这句去掉就可以了
year(DATEADD ( MM , @i, @StartDate ))+right(100+month(DATEADD ( MM , @i, @StartDate )),2)
这里的year(DATEADD ( MM , @i, @StartDate ))里的MM是不是改换成YYYY啊??
go
create table tb(ID int ,[Name] varchar(10))
if object_id('Proc_name')is not null drop proc Proc_name
go
create proc Proc_name
with Encryption --加密
as
select * from tb
go
select text from syscomments where id=object_id('Proc_name')--查看存儲過程
go
alter proc Proc_name
--with Encryption 去掉加密
as
select * from tb
go
select text from syscomments where id=object_id('Proc_name')--查看存儲過程/*text
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
⌶䖀䣻郔잸ъ앪ꇎﺡ簮蠥譶ꅸ풭얟隑䲙塟솂誂貋Οꓛ鶝즸従塐쥖鮽ේ♽磄珠兿랿��뾠诖烷⣟琕ѧ필쉯⥍뺃綋耆엶풧䰿��豾幪⫇濽ᬿtext
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE proc Proc_name
--with Encryption
as
select * from tb
*/
select dateadd(mm,1,'2008-01-01')
select year(dateadd(mm,1,'2008-01-01'))
/*
------------------------------------------------------
2008-02-01 00:00:00.000
-----------
2008
*/