问题描述

sql server创建存储过程初始化失败，不知道为什么，求大侠帮忙看看

CREATE PROCEDURE p_getLogFilter
@account VARCHAR(20),
@split VARCHAR(10)
AS
BEGIN
DECLARE @splitlen INT,
@au_id CHAR(50),
@s VARCHAR(MAX),
@cform VARCHAR(255),
@dbname VARCHAR(200),
@sqlstr NVARCHAR(MAX)
--获取数据库名
SELECT @dbname=dbname FROM accinfo WHERE accid=@account
--创建临时表
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..##templogdescp') AND type='U')
DROP TABLE ##templogdescp

CREATE TABLE ##templogdescp(operate VARCHAR(MAX),cform VARCHAR(255))
DELETE FROM ##templogdescp

SET @sqlstr = 'select @au_id = min( cguid ) from ['+@dbname+'].[dbo].[CM_LOG] where ctype=1 and caction=''查询'''
EXEC SP_EXECUTESQL @sqlstr,N'@au_id char(20) OUTPUT', @au_id OUTPUT
--select @au_id = min( cguid ) from CM_LOG where ctype=1 and caction='查询'

SET @splitlen=LEN(@split+'a')-2
WHILE @au_id IS NOT NULL
BEGIN
--set @s = (select substring(CDESCP,charindex('[',CDESCP)+1,len(CDESCP)-(case when charindex('[',CDESCP)!=0 then charindex('[',CDESCP)+1 else 0 end)) from [A399992014].[dbo].CM_LOG where cguid = @au_id)
--set @cform = (select cform from [A399992014].[dbo].CM_LOG where cguid=@au_id)
SET @sqlstr = 'select @s=substring(CDESCP,charindex(''['',CDESCP)+1,len(CDESCP)-(case when charindex(''['',CDESCP)!=0 then charindex(''['',CDESCP)+1 else 0 end)) from ['+@dbname+'].[dbo].CM_LOG where cguid = '''+@au_id+''''
EXEC SP_EXECUTESQL @sqlstr,N'@s varchar(max) OUTPUT', @s OUTPUT
SET @sqlstr = 'select @cform=cform from ['+@dbname+'].[dbo].CM_LOG where cguid='''+@au_id+''''
EXEC SP_EXECUTESQL @sqlstr,N'@cform varchar(255) OUTPUT', @cform OUTPUT
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT ##templogdescp VALUES(LEFT(@s,CHARINDEX(@split,@s)-1),@cform)
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END

 IF(@s IS NOT NULL)
 INSERT ##templogdescp VALUES(@s,@cform)

 --select @au_id = min( cguid ) from [A399992014].[dbo].CM_LOG where cguid > @au_id and ctype=1 and caction='查询'
 SET @sqlstr = 'select @au_id = min( cguid ) from ['+@dbname+'].[dbo].CM_LOG where cguid > '''+@au_id+''' and ctype=1 and caction=''查询'''
EXEC SP_EXECUTESQL  @sqlstr,N'@au_id varchar(255) OUTPUT', @au_id OUTPUT

END

RETURN
END