- SQL code
CREATE PROCEDURE P_Search_ShitfList@S_BeginTime datetime ,--开始时间 是由查询的医生提供@S_EndTime datetime ,--结束时间@DeptID NVarChar --部门编号ASdeclare @ShiftCount intdeclare @ShiftID intdeclare cursor iTouch for select * from shift where deptID= @DeptIDselect @ShiftCount=count(*) from shift where deptID= @DeptID while @ShiftCount<>0 begin--我想在这个地方遍历,取出ShiftID字段--exec P_Search_Shitf '2012-02-01','2012-02-28','3','8'set @[email protected]endGO
他们说要用游标,我这样建立又出错
请问该怎么做,谢谢
------解决方案--------------------
- SQL code
USE AdventureWorks2008R2;GO-- Declare the variables to store the values returned by FETCH.DECLARE @LastName varchar(50), @FirstName varchar(50);DECLARE contact_cursor CURSOR FORSELECT LastName, FirstName FROM Person.PersonWHERE LastName LIKE 'B%'ORDER BY LastName, FirstName;OPEN contact_cursor;-- Perform the first fetch and store the values in variables.-- Note: The variables are in the same order as the columns-- in the SELECT statement.FETCH NEXT FROM contact_cursorINTO @LastName, @FirstName;-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN-- Concatenate and display the current values in the variables.PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM contact_cursorINTO @LastName, @FirstName;ENDCLOSE contact_cursor;DEALLOCATE contact_cursor;GO
------解决方案--------------------
去掉
select @ShiftCount=count(*) from shift
where deptID= @DeptID
判断游标状态用@@fetch_status
WHILE @@fetch_status = 0
begin
end
具体看下游标的用法吧
很基本的
------解决方案--------------------
go
if OBJECT_ID('shift')is not null
drop table shift
go
create table shift(
ID int,
deptid int,
riqi datetime
)
go
insert shift
select 1,1,'2011-12-31' union all
select 2,2,'2011-6-25' union all
select 3,1,'2011-9-30' union all
select 4,1,'2011-05-12' union all
select 5,2,'2011-12-24' union all
select 6,3,'2011-12-11'
select id from shift where deptid=1 and riqi between '2011-6-01'and'2011-10-31'
go
if OBJECT_ID('P_Search_ShitfList') is not null
drop proc P_Search_ShitfList
go
create proc P_Search_ShitfList
@S_BeginTime datetime ,--开始时间 是由查询的医生提供
@S_EndTime datetime ,--结束时间
@DeptID NVarChar --部门编号
as
declare Shitf_Cur cursor
for select id from shift
where [email protected]
and riqi between @S_BeginTime and @S_EndTime
declare @ShiftID int
open Shitf_Cur
fetch next from Shitf_Cur into @ShiftID
while @@fetch_status=0
begin
print ltrim(@ShiftID)
fetch next from Shitf_Cur into @ShiftID
end
close Shitf_Cur
deallocate Shitf_Cur
exec P_Search_ShitfList '2011-6-01','2011-10-31',1
楼主把游标名看仔细了,你的存储过程中游标名不一致,我已经改过来了
你看看我这个,没问题了