SQL stored procedure example

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) = '2017-01-01 ',
@ Endday nvarchar (50) = '2017-01-01'
As
Begin

-- Declare a cursor
Declare checkandratifycursor cursor
Select real_name, party_id from person

-- Open the cursor
Open checkandratifycursor

-- Declare a temporary table
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 a variable
Declare @ realname varchar (50)
Declare @ partyid varchar (50)

-- Move cyclically
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 + ';', ';' +. 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) = '2017-01-01 ',
@ Endday nvarchar (50) = '2017-01-01'
As
Begin

-- Declare a cursor
Declare checkupcursor cursor
Select real_name, party_id from person

-- Open the cursor
Open checkupcursor

-- Declare a temporary table
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 a variable
Declare @ realname varchar (50)
Declare @ partyid varchar (50)

-- Move cyclically
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 + ';', ';' +. 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) = '2017-01-01 ',
@ Endday nvarchar (50) = '2017-01-01'
As
Begin

-- Declare a cursor
Declare itemprincipalcursor cursor
Select real_name, party_id from person

-- Open the cursor
Open itemprincipalcursor

-- Declare a temporary table
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 a variable
Declare @ realname varchar (50)
Declare @ partyid varchar (50)

-- Move cyclically
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 + ';', ';' +. 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) = '2017-01-01 ',
@ Endday nvarchar (50) = '2017-01-01'
As
Begin

-- Declare a cursor
Declare mycursor cursor
Select organize_name from organize

-- Open the cursor
Open mycursor

-- Declare a temporary table
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 a variable
Declare @ deptname varchar (50)

-- Move cyclically
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) = '2017-01-01 ',
-- @ Endday nvarchar (50) = '2017-01-01'
As
Begin

-- Declare a cursor
Declare powrole cursor
Select role_id, role_name from pow_role

-- Open the cursor
Open powrole

-- Declare a temporary table
/*
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 a variable
Declare @ realname varchar (50)
Declare @ partyid varchar (50)

-- Move cyclically
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