CREATE procedure [dbo].[UP_GetBillStatusByUserId]
(
@UserId int = 0, --用户Id
@BillType int =0--单据类型
)
AS
DECLARE @BillStatusList nvarchar(200)='' --返回拼接的状态串
DECLARE @DeptId int=0
DECLARE @BillStatus char(10)=''
IF not exists(select * from sys.syscursors where cursor_name='MyBillStatusCursor')
BEGIN
DECLARE MyBillStatusCursor CURSOR LOCAL FOR
SELECT DeptId FROM T_UserDept WHERE UserId=@UserId
End
----BillType=3
IF(@BillType=3)
BEGIN
OPEN MyBillStatusCursor
FETCH NEXT FROM MyBillStatusCursor INTO @DeptId
WHILE(@@FETCH_STATUS =0)
BEGIN
SELECT @BillStatus=BillStatus FROM T_DOAPExamDept WHERE DeptId=@DeptId
IF(@BillStatus IS NOT NULL AND LEN(@BillStatus)>0)
BEGIN
SET @BillStatusList=@BillStatusList+RTRIM(@BillStatus)+','
END
SELECT @BillStatus=MIN(RejectBillStatus) FROM T_DOAPExamDept WHERE RejectBillStatus>@BillStatus
IF(@BillStatus IS NOT NULL AND LEN(@BillStatus)>0)
BEGIN
SET @BillStatusList=@BillStatusList+RTRIM(@BillStatus)+','
END
FETCH NEXT FROM MyBillStatusCursor INTO @DeptId
END
CLOSE MyBillStatusCursor
END
---BillType=4
ELSE IF(@BillType=4)
BEGIN
OPEN MyBillStatusCursor
FETCH NEXT FROM MyBillStatusCursor INTO @DeptId
WHILE(@@FETCH_STATUS =0)
BEGIN
SELECT @BillStatus=BillStatus FROM T_DOAMExamDept WHERE DeptId=@DeptId
IF(@BillStatus IS NOT NULL AND LEN(@BillStatus)>0)
BEGIN
SET @BillStatusList=@BillStatusList+RTRIM(@BillStatus)+','
END
SELECT @BillStatus=MIN(RejectBillStatus) FROM T_DOAMExamDept WHERE RejectBillStatus>@BillStatus
IF(@BillStatus IS NOT NULL AND LEN(@BillStatus)>0)
BEGIN
SET @BillStatusList=@BillStatusList+RTRIM(@BillStatus)+','
END
FETCH NEXT FROM MyBillStatusCursor INTO @DeptId
END
CLOSE MyBillStatusCursor
END
SELECT @BillStatusList
相当拙劣吧,呵呵
========================================
这是网上一些解决方法,我直接copy的,
-------------------------------
-- 参考下面的例子
declare @stmt nvarchar(200), @param nvarchar(40);
set @stmt=N'declare c cursor global for select * from authors where state=@state';
set @param=N'@state char(2)';
exec sp_executesql @stmt,@param,'CA';
open c;
fetch next from c;
close c;
deallocate c;
-----------------------------------------
exec('
declare curOne cursor global
for' + @aSql);
declare @str nvarchar(20);
open curOne
fetch curOne into @str;
while(@@fetch_status=0)
begin
...
fetch curOne into @str;
end;
close curone;
deallocate curone;
旁白:和上一个如此相似,不过上一个更有含金量
--------------------------------------
CREATE TABLE #tabTmp(id int)
INSERT #tabTmp EXECUTE('SELECT id FROM '+@TableName)
declare curOne cursor for
SELECT id FROM #tabTmp
open curOne
FETCH NEXT from curOne into @ID
画外音:这个用到了临时表
-----------------------------------------------
END
去喝口茶吧。