SELECT @TMPAUTHOR = @TMPAUTHOR+'_'
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
------
上面的是不能共用一个变量的。
改为:
declare @TMPAUTHORS varchar(1000)
SELECT @TMPAUTHORS= @TMPAUTHORS+'-'+@TMPAUTHOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
------
上面的是不能共用一个变量的。
改为:
declare @TMPAUTHORS varchar(1000)
SELECT @TMPAUTHORS= @TMPAUTHORS+'-'+@TMPAUTHOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
declare @a varchar(200) DECLARE AUTHORCURSOR CURSOR FOR SELECT AUTHOR_NO FROM USER_AUTHOR WHERE LOGIN_NAME = @LOGINNAME
OPEN AUTHORCURSOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @a= @a + '_' + @TMPAUTHOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
END
CLOSE AUTHORCURSOR
DEALLOCATE AUTHORCURSOR
SELECT @AUTHOR = @a
这样就行了!
如果varchar nvarchar这种数据类型在初始化的时候为NULL或字符空是不同的,比如
@char1 NVARCHAR(10) = NULL
@char2 NVARCHAR(10) = N''
比如一下的代码就完全没有问题了,好像如果变量为NULL,那么使用游标赋值赋不进去。如果改成''那么就可以了declare @TMPAUTHOR NVARCHAR(10)
declare @TMPAUTHORS NVARCHAR(10)
SELECT @TMPAUTHOR = N''
SELECT @TMPAUTHORS = N''
IF @AUTHORCOUNT > 0
BEGIN
--使用游标
DECLARE AUTHORCURSOR CURSOR FOR SELECT AUTHOR_NO FROM USER_AUTHOR WHERE LOGIN_NAME = @LOGINNAME
OPEN AUTHORCURSOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
WHILE(@@FETCH_STATUS=0)
BEGIN
IF @@TMPAUTHORS = N''
BEGIN
SELECT @@TMPAUTHORS = @TMPAUTHOR
END
ELSE
BEGIN
SELECT @@TMPAUTHORS = @@TMPAUTHORS+N'_'+@TMPAUTHOR
END
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
END
CLOSE AUTHORCURSOR
DEALLOCATE AUTHORCURSOR
SELECT @AUTHOR = @@TMPAUTHORS
END
ELSE
BEGIN
SELECT @AUTHOR = N''
END
EN
OPEN AUTHORCURSOR
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @TMPAUTHOR = isnull(@TMPAUTHOR,'')+'_'
FETCH NEXT FROM AUTHORCURSOR INTO @TMPAUTHOR
END
CLOSE AUTHORCURSOR
DEALLOCATE AUTHORCURSOR
SELECT @AUTHOR = @TMPAUTHOR