sql存储过程小例子

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckAndRatify_Stat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckAndRatify_Stat]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CheckAndRatify_Stat         
@startday nvarchar(50)='1900-01-01',
@endday nvarchar(50)='9999-01-01'
AS
BEGIN

--声明一个游标
DECLARE  CheckAndRatifyCursor  CURSOR FOR
SELECT real_name,Party_Id  FROM PERSON

--打开游标
open CheckAndRatifyCursor

--声明一个临时表
create table #temp(
REALNAME varchar(50),
ITEMNAME varchar (128),
MANAGERLEVEL varchar (32),
ITEMSERIALNUM varchar (255),
DESIGNSTAGE varchar (255),
ITEMPRINCIPALCN varchar (255),
CHECKUPCN varchar (255),
CHECKANDRATIFYCN varchar (32),
LEADERCN varchar (255),
TRUSTUNITNAMECN varchar (128),
SIGN_2 varchar (32),
DELIVERDATE varchar (100),
UNDERTAKDEPTECN varchar (160)
)

--声明一个变量
declare @realname varchar(50)
declare @partyId varchar(50)

--循环移动
fetch next from CheckAndRatifyCursor into @realname, @partyId
while(@@fetch_status=0)
  begin
    insert into  #temp  SELECT @realname AS REALNAME, a.ITEMNAME,a.MANAGERLEVEL, a.ITEMSERIALNUM, a.DESIGNSTAGE,
      a.ITEMPRINCIPALCN, a.CHECKUPCN, a.CHECKANDRATIFYCN, a.LEADERCN,
      a.TRUSTUNITNAMECN, a.SIGN_2, a.DELIVERDATE, a.UNDERTAKDEPTECN
FROM PROJECT_BASICINFO a INNER JOIN
      WORK_EFFORT c ON a.ITEMID = c.SOURCE_REFERENCE_ID
WHERE (c.WORK_EFFORT_TYPE_ID = 'WORK_FLOW') AND
      (c.CURRENT_STATUS_ID = 'WF_COMPLETED') and  CHARINDEX( ';'+@partyId+';',';'+a.CHECKANDRATIFY+';',1)>0
 
    fetch next from CheckAndRatifyCursor into @realname, @partyId
  end

close CheckAndRatifyCursor
deallocate CheckAndRatifyCursor

select * from #temp where DELIVERDATE>=@startday  and  DELIVERDATE<=@endday

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUp_Stat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckUp_Stat]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE CheckUp_Stat         
@startday nvarchar(50)='1900-01-01',
@endday nvarchar(50)='9999-01-01'
AS
BEGIN

--声明一个游标
DECLARE  CheckUpCursor  CURSOR FOR
SELECT real_name,Party_Id  FROM PERSON

--打开游标
open CheckUpCursor

--声明一个临时表
create table #temp(
REALNAME varchar(50),
MANAGERLEVEL varchar (32),
ITEMNAME varchar (128),
ITEMSERIALNUM varchar (255),
DESIGNSTAGE varchar (255),
ITEMPRINCIPALCN varchar (255),
CHECKUPCN varchar (255),
CHECKANDRATIFYCN varchar (32),
LEADERCN varchar (255),
TRUSTUNITNAMECN varchar (128),
SIGN_2 varchar (32),
DELIVERDATE varchar (100),
UNDERTAKDEPTECN varchar (160)
)

--声明一个变量
declare @realname varchar(50)
declare @partyId varchar(50)

--循环移动
fetch next from CheckUpCursor into @realname, @partyId
while(@@fetch_status=0)
  begin
    insert into  #temp  SELECT @realname AS REALNAME,a.MANAGERLEVEL, a.ITEMNAME, a.ITEMSERIALNUM, a.DESIGNSTAGE,
      a.ITEMPRINCIPALCN, a.CHECKUPCN, a.CHECKANDRATIFYCN, a.LEADERCN,
      a.TRUSTUNITNAMECN, a.SIGN_2, a.DELIVERDATE, a.UNDERTAKDEPTECN
FROM PROJECT_BASICINFO a INNER JOIN
      WORK_EFFORT c ON a.ITEMID = c.SOURCE_REFERENCE_ID
WHERE (c.WORK_EFFORT_TYPE_ID = 'WORK_FLOW') AND
      (c.CURRENT_STATUS_ID = 'WF_COMPLETED') and  CHARINDEX( ';'+@partyId+';',';'+a.CHECKUP+';',1)>0
 
    fetch next from CheckUpCursor into @realname, @partyId
  end

close CheckUpCursor
deallocate CheckUpCursor

select * from #temp where DELIVERDATE>=@startday  and  DELIVERDATE<=@endday

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ITEMPRINCIPAL_Stat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ITEMPRINCIPAL_Stat]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ITEMPRINCIPAL_Stat         
@startday nvarchar(50)='1900-01-01',
@endday nvarchar(50)='9999-01-01'
AS
BEGIN

--声明一个游标
DECLARE  ITEMPRINCIPALCURSOR  CURSOR FOR
SELECT real_name,Party_Id  FROM PERSON

--打开游标
open ITEMPRINCIPALCURSOR

--声明一个临时表
create table #temp(
REALNAME varchar(50),
MANAGERLEVEL varchar (32),
ITEMNAME varchar (128),
ITEMSERIALNUM varchar (255),
DESIGNSTAGE varchar (255),
ITEMPRINCIPALCN varchar (255),
CHECKUPCN varchar (255),
CHECKANDRATIFYCN varchar (32),
LEADERCN varchar (255),
TRUSTUNITNAMECN varchar (128),
SIGN_2 varchar (32),
DELIVERDATE varchar (100),
UNDERTAKDEPTECN varchar (160)
)

--声明一个变量
declare @realname varchar(50)
declare @partyId varchar(50)

--循环移动
fetch next from  ITEMPRINCIPALCURSOR into @realname, @partyId
while(@@fetch_status=0)
  begin
    insert into  #temp  SELECT @realname AS REALNAME,a.MANAGERLEVEL, a.ITEMNAME, a.ITEMSERIALNUM, a.DESIGNSTAGE,
      a.ITEMPRINCIPALCN, a.CHECKUPCN, a.CHECKANDRATIFYCN, a.LEADERCN,
      a.TRUSTUNITNAMECN, a.SIGN_2, a.DELIVERDATE, a.UNDERTAKDEPTECN
FROM PROJECT_BASICINFO a INNER JOIN
      WORK_EFFORT c ON a.ITEMID = c.SOURCE_REFERENCE_ID
WHERE (c.WORK_EFFORT_TYPE_ID = 'WORK_FLOW') AND
      (c.CURRENT_STATUS_ID = 'WF_COMPLETED') and  CHARINDEX( ';'+@partyId+';',';'+a.ITEMPRINCIPAL+';',1)>0
 
    fetch next from ITEMPRINCIPALCURSOR into @realname, @partyId
  end

close ITEMPRINCIPALCURSOR
deallocate ITEMPRINCIPALCURSOR

select * from #temp where DELIVERDATE>=@startday  and  DELIVERDATE<=@endday

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stat1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stat1]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE stat1         
@startday nvarchar(50)='1900-01-01',
@endday nvarchar(50)='9999-01-01'
AS
BEGIN

--声明一个游标
DECLARE MyCURSOR CURSOR FOR
SELECT ORGANIZE_NAME FROM ORGANIZE

--打开游标
open MyCURSOR

--声明一个临时表
create table #temp(
DEPTNAME varchar(50),
MANAGERLEVEL varchar (32),
ITEMNAME varchar (128),
ITEMSERIALNUM varchar (255),
DESIGNSTAGE varchar (255),
ITEMPRINCIPALCN varchar (255),
CHECKUPCN varchar (255),
CHECKANDRATIFYCN varchar (32),
LEADERCN varchar (255),
TRUSTUNITNAMECN varchar (128),
SIGN_2 varchar (32),
DELIVERDATE varchar (100),
UNDERTAKDEPTECN varchar (160)
)

--声明一个变量
declare @deptName varchar(50)

--循环移动
fetch next from MyCURSOR into @deptName
while(@@fetch_status=0)
  begin
    insert into  #temp  SELECT @deptName AS DEPTNAME,a.MANAGERLEVEL, a.ITEMNAME, a.ITEMSERIALNUM, a.DESIGNSTAGE,
      a.ITEMPRINCIPALCN, a.CHECKUPCN, a.CHECKANDRATIFYCN, a.LEADERCN,
      a.TRUSTUNITNAMECN, a.SIGN_2, a.DELIVERDATE, a.UNDERTAKDEPTECN
FROM PROJECT_BASICINFO a INNER JOIN
      WORK_EFFORT c ON a.ITEMID = c.SOURCE_REFERENCE_ID
WHERE (c.WORK_EFFORT_TYPE_ID = 'WORK_FLOW') AND
      (c.CURRENT_STATUS_ID = 'WF_COMPLETED') and  CHARINDEX( @deptName,a.UNDERTAKDEPTECN,1)>0
 
    fetch next from MyCURSOR into @deptName
  end

close MyCURSOR
deallocate MyCURSOR

select * from #temp where DELIVERDATE>=@startday  and  DELIVERDATE<=@endday

END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

***************************************************************************

CREATE PROCEDURE testInsert_p         
--@startday nvarchar(50)='1900-01-01',
--@endday nvarchar(50)='9999-01-01'
AS
BEGIN

--声明一个游标
DECLARE  powRole  CURSOR FOR
SELECT ROLE_ID,ROLE_NAME  FROM POW_ROLE

--打开游标
open powRole

--声明一个临时表
/*
create table #temp(

REALNAME varchar(50),
ITEMNAME varchar (128),
MANAGERLEVEL varchar (32),
ITEMSERIALNUM varchar (255),
DESIGNSTAGE varchar (255),
ITEMPRINCIPALCN varchar (255),
CHECKUPCN varchar (255),
CHECKANDRATIFYCN varchar (32),
LEADERCN varchar (255),
TRUSTUNITNAMECN varchar (128),
SIGN_2 varchar (32),
DELIVERDATE varchar (100),
UNDERTAKDEPTECN varchar (160)
)
*/
--声明一个变量
declare @realname varchar(50)
declare @partyId varchar(50)

--循环移动
fetch next from powRole into  @partyId, @realname
while(@@fetch_status=0)
  begin
    insert into testinsert values (@partyId, @realname) 
 
    fetch next from powRole into @realname, @partyId
  end

close powRole
deallocate powRole

--select * from #temp where DELIVERDATE>=@startday  and  DELIVERDATE<=@endday

END