create table UserRight(rUserID varchar(10),rFormName varchar(20),rView bit)
insert UserRight select 'CatchWind','frmCadre' ,1
union all select 'CatchWind','frmCadreDimiss',0
union all select 'CatchWind','frmDept' ,1
union all select 'CatchWind','frmDeptChange' ,1
goCREATE Proc SP_USERRIGHT(@UserID varchar(20))
ASDECLARE @SQLA varchar(3000) --創建臨時表
DECLARE @SQLB varchar(1000) --更新權限數據
SET @SQLA=''
SET @SQLB=''
DECLARE @FormName varchar(50)
DECLARE @Rig bit DECLARE User_Right CURSOR FAST_FORWARD
FOR SELECT rFormName,rView from UserRight where rUserId=@UserID
OPEN User_Right FETCH NEXT FROM User_Right INTO @FormName,@Rig
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQLA=@SQLA + @FormName + ' bit ,'
SET @SQLB=@SQLB + LTRIM(STR(@Rig)) + ','
FETCH NEXT FROM User_Right INTO @FormName,@Rig
END
CLOSE User_Right
DEALLOCATE User_RightSET @SQLA=LEFT(@SQLA,LEN(@SQLA)-1)
SET @SQLB=LEFT(@SQLB,LEN(@SQLB)-1)exec('
Create Table #Rights (' + @SQLA + ')
INSERT #Rights VALUES(' + @SQLB + ')
SELECT * FROM #Rights
')
goexec SP_USERRIGHT 'CatchWind'
godrop table UserRight
drop proc SP_USERRIGHT/*--测试结果frmCadre frmCadreDimiss frmDept frmDeptChange
-------- -------------- ------- -------------
1 0 1 1(所影响的行数为 1 行)
--*/
insert UserRight select 'CatchWind','frmCadre' ,1
union all select 'CatchWind','frmCadreDimiss',0
union all select 'CatchWind','frmDept' ,1
union all select 'CatchWind','frmDeptChange' ,1
goCREATE Proc SP_USERRIGHT(@UserID varchar(20))
ASDECLARE @SQLA varchar(3000) --創建臨時表
DECLARE @SQLB varchar(1000) --更新權限數據
SET @SQLA=''
SET @SQLB=''
DECLARE @FormName varchar(50)
DECLARE @Rig bit DECLARE User_Right CURSOR FAST_FORWARD
FOR SELECT rFormName,rView from UserRight where rUserId=@UserID
OPEN User_Right FETCH NEXT FROM User_Right INTO @FormName,@Rig
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQLA=@SQLA + @FormName + ' bit ,'
SET @SQLB=@SQLB + LTRIM(STR(@Rig)) + ','
FETCH NEXT FROM User_Right INTO @FormName,@Rig
END
CLOSE User_Right
DEALLOCATE User_RightSET @SQLA=LEFT(@SQLA,LEN(@SQLA)-1)
SET @SQLB=LEFT(@SQLB,LEN(@SQLB)-1)exec('
Create Table #Rights (' + @SQLA + ')
INSERT #Rights VALUES(' + @SQLB + ')
SELECT * FROM #Rights
')
goexec SP_USERRIGHT 'CatchWind'
godrop table UserRight
drop proc SP_USERRIGHT/*--测试结果frmCadre frmCadreDimiss frmDept frmDeptChange
-------- -------------- ------- -------------
1 0 1 1(所影响的行数为 1 行)
--*/
1:把语句都直接实现,不要使用exec(@Str)的方式
2:把语句都放在@Str中,使用exec(@Str)方式实现
比如你过程中的最后一句:set @SQLB='SELECT * FROM #Rights'
EXEC (@SQLB)