日期:2014-05-18  浏览次数:20631 次

帮忙看下这个存储过程(在线等)
set   ANSI_NULLS   ON
set   QUOTED_IDENTIFIER   ON
go

ALTER   procedure   [sys].[sp_addextendedproperty]
@name   sysname,
@value   sql_variant =   NULL,
@level0type varchar(128) =   NULL,
@level0name sysname =   NULL,
@level1type varchar(128) =   NULL,
@level1name sysname =   NULL,
@level2type varchar(128) =   NULL,
@level2name sysname =   NULL
as

declare   @ret   int

if   datalength(@value)   >   7500
begin
raiserror(15097,-1,-1)
return   1
end

if   @name   is   null
begin
raiserror(15600,-1,-1, 'sp_addextendedproperty ')
return   (1)
end

execute   @ret   =   sys.sp_validname   @name
if   (@ret   <>   0)
begin
raiserror(15600,-1,-1, 'sp_addextendedproperty ')
return   (1)
end
               

BEGIN   TRANSACTION
SAVE   TRANSACTION   SP_ADDEXTENDEDPROPERTY

begin
EXEC   %%ExtendedPropertySet().AddValue(Name   =   @name,   Value   =   @value,   Level0type   =   @level0type,   Level0name   =   @level0name,   Level1type   =   @level1type,   Level1name   =   @level1name,   Level2type   =   @level2type,   Level2name   =   @level2name)
IF   @@error   <>   0
begin
ROLLBACK   TRANSACTION   SP_ADDEXTENDEDPROPERTY
COMMIT   TRANSACTION
return   (1)
end
end

COMMIT   TRANSACTION
return   (0)

上面的存储过程在Sql   Server   2005中是正常的,在Sql   Server   2000中提示错误“第   39   行:   '% '   附近有语法错误。”
大家帮忙看看啊,谢谢

------解决方案--------------------
EXEC %%ExtendedPropertySet().AddValue()

Sql2000是不支持这样的用法的,CLR是Sql2005的新功能
------解决方案--------------------
在Sql Server 2005中也报错:
Msg 102, Level 15, State 1, Procedure sp_addextendedproperty, Line 39
Incorrect syntax near '% '.