34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE P_Search_ShitfList
@S_BeginTime datetime ,--开始时间 是由查询的医生提供
@S_EndTime datetime ,--结束时间
@DeptID NVarChar --部门编号
AS
declare @ShiftCount int
declare @ShiftID int
declare cursor iTouch for select * from shift where deptID= @DeptID
select @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 @ShiftCount=@ShiftCount-1
end
GO
CREATE TABLE [dbo].[Shift] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[DeptID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[BeginTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[Descript] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cancel] [bit] NULL ,
[ManCreate] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ManModify] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TimeCreate] [datetime] NULL ,
[TimeModify] [datetime] NULL
) ON [PRIMARY]
declare Shitf_Cur cursor for select id from shift where deptID= @DeptID
CREATE PROCEDURE P_Search_ShitfList
@S_BeginTime datetime ,--开始时间 是由查询的医生提供
@S_EndTime datetime ,--结束时间
@DeptID NVarChar --部门编号
AS
declare @ShiftID int
declare Shitf_Cur cursor for select id from shift where deptID= @DeptID
open Shift_Cur
fetch next from Shift_Cur into @ShiftID
while @@fetch_status=0
begin
--exec P_Search_Shitf '2012-02-01','2012-02-28','3','8'
print ltrim(str(@ShiftID))
fetch next from Shift_Cur into @ShiftID
end
close Shift_Cur
deallocate Shift_Cur
GO
exec P_Search_ShitfList '2012-02-01','2012-02-28','3'
USE AdventureWorks2008R2;
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50);
DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Person
WHERE 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_cursor
INTO @LastName, @FirstName;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- 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_cursor
INTO @LastName, @FirstName;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
GO