286 lines
12 KiB
Transact-SQL
286 lines
12 KiB
Transact-SQL
if exists (select * from sysobjects where id = object_id(N'[dbo].[GetQryOP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[GetQryOP]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[GetRCIncidentDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[GetRCIncidentDetail]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[GetRCIncidentsQryFieldSE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[GetRCIncidentsQryFieldSE]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[RunRCIncidentQueryCust]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[RunRCIncidentQueryCust]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[RunRCIncidentQuerySE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[RunRCIncidentQuerySE]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_AddRCIncident]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[sp_AddRCIncident]
|
||
GO
|
||
|
||
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_UpdateRCIncident]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
|
||
drop procedure [dbo].[sp_UpdateRCIncident]
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
|
||
GO
|
||
|
||
CREATE PROC dbo.GetQryOP
|
||
As
|
||
Select * From TblQryOP
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
|
||
GO
|
||
|
||
|
||
CREATE Proc dbo.GetRCIncidentDetail
|
||
@iIncidentID int
|
||
As
|
||
Select sUserName, sDescription, sFile
|
||
from TblRCIncidents
|
||
where iIncidentID = @iIncidentID
|
||
|
||
return @@ERROR
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
|
||
|
||
CREATE Proc dbo.GetRCIncidentsQryFieldSE
|
||
As
|
||
Select * from TblRCIncidentsQryFieldSE
|
||
Order by sFieldName
|
||
|
||
|
||
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
|
||
GO
|
||
|
||
|
||
CREATE Proc dbo.RunRCIncidentQueryCust
|
||
-- @sFrom nvarchar (1000),
|
||
-- We probably dont need this sFrom, since we select from
|
||
-- a single table.
|
||
@sWhere nvarchar (2000),
|
||
@iStart int, -- 1 ~ N-1
|
||
@iGet int,
|
||
@iTotal int OUTPUT,
|
||
@iRet int OUTPUT
|
||
As
|
||
Begin
|
||
Declare @sExec nvarchar (3000)
|
||
Declare @i int, @j int, @iIncd int
|
||
Declare @ErrorStatus int
|
||
|
||
Select @iRet = 0
|
||
Select @ErrorStatus = 0
|
||
Set @sWhere = N'(' + @sWhere + N')'
|
||
|
||
Create Table #tblTmp (iIncd int)
|
||
|
||
Set NoCount ON
|
||
Set @sExec = N'Insert #tblTmp (iIncd) Select Distinct iIncidentID
|
||
From ' + N'TblRCIncidents' + N' Where ' +
|
||
@sWhere
|
||
Exec (@sExec)
|
||
|
||
Select @iTotal = Count (*) from #tblTmp
|
||
If @iTotal IS NULL SET @iTotal = 0
|
||
If (@iStart > @iTotal) OR (@iStart + @iGet > @iTotal)
|
||
Begin
|
||
If (@iStart > @iTotal) Goto lblExit
|
||
Select @iGet = @iTotal - @iStart + 1
|
||
End
|
||
Set @i = @iStart + @iGet - 1
|
||
Declare tbl_Cursor CURSOR LOCAL READ_ONLY For
|
||
Select * from #tblTmp
|
||
Open tbl_Cursor
|
||
While @iStart > 1
|
||
Begin
|
||
Fetch Next From tbl_Cursor into @iIncd
|
||
Set @iStart = @iStart - 1
|
||
End
|
||
Set @iRet = @iGet
|
||
Create Table #tmp1 (iIncd int)
|
||
While @iGet > 0
|
||
Begin
|
||
Fetch Next From tbl_Cursor into @iIncd
|
||
Insert into #tmp1 Values (@iIncd)
|
||
Set @iGet = @iGet - 1
|
||
If @@FETCH_STATUS <> 0 Break
|
||
End
|
||
Set @iRet = @iRet - @iGet
|
||
|
||
Select a.iIncidentID, dtUploadDate, sDescription, iStatus
|
||
From TblRCIncidents a, #tmp1 b
|
||
Where a.iIncidentID = b.iIncd
|
||
|
||
Drop Table #tmp1
|
||
Close tbl_Cursor
|
||
Deallocate tbl_Cursor
|
||
|
||
lblExit:
|
||
Set NoCount Off
|
||
Drop Table #tblTmp
|
||
Return @ErrorStatus
|
||
End
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
|
||
GO
|
||
|
||
|
||
|
||
CREATE Proc dbo.RunRCIncidentQuerySE
|
||
-- @sFrom nvarchar (1000),
|
||
-- We probably dont need this sFrom, since we select from
|
||
-- a single table.
|
||
@sWhere nvarchar (2000),
|
||
@iStart int, -- 1 ~ N-1
|
||
@iGet int,
|
||
@iTotal int OUTPUT,
|
||
@iRet int OUTPUT
|
||
As
|
||
Begin
|
||
Declare @sExec nvarchar (3000)
|
||
Declare @i int, @j int, @iIncd int
|
||
Declare @ErrorStatus int
|
||
|
||
Select @iRet = 0
|
||
Select @ErrorStatus = 0
|
||
Set @sWhere = N'(' + @sWhere + N')'
|
||
|
||
Create Table #tblTmp (iIncd int)
|
||
|
||
Set NoCount ON
|
||
Set @sExec = N'Insert #tblTmp (iIncd) Select Distinct iIncidentID
|
||
From ' + N'TblRCIncidents' + N' Where ' +
|
||
@sWhere
|
||
Exec (@sExec)
|
||
|
||
Select @iTotal = Count (*) from #tblTmp
|
||
If @iTotal IS NULL SET @iTotal = 0
|
||
If (@iStart > @iTotal) OR (@iStart + @iGet > @iTotal)
|
||
Begin
|
||
If (@iStart > @iTotal) Goto lblExit
|
||
Select @iGet = @iTotal - @iStart + 1
|
||
End
|
||
Set @i = @iStart + @iGet - 1
|
||
Declare tbl_Cursor CURSOR LOCAL READ_ONLY For
|
||
Select * from #tblTmp
|
||
Open tbl_Cursor
|
||
While @iStart > 1
|
||
Begin
|
||
Fetch Next From tbl_Cursor into @iIncd
|
||
Set @iStart = @iStart - 1
|
||
End
|
||
Set @iRet = @iGet
|
||
Create Table #tmp1 (iIncd int)
|
||
While @iGet > 0
|
||
Begin
|
||
Fetch Next From tbl_Cursor into @iIncd
|
||
Insert into #tmp1 Values (@iIncd)
|
||
Set @iGet = @iGet - 1
|
||
If @@FETCH_STATUS <> 0 Break
|
||
End
|
||
Set @iRet = @iRet - @iGet
|
||
|
||
Select a.iIncidentID, sUserName, dtUploadDate, iMemberLow,
|
||
iMemberHigh, iStatus
|
||
From TblRCIncidents a, #tmp1 b
|
||
Where a.iIncidentID = b.iIncd
|
||
|
||
Drop Table #tmp1
|
||
Close tbl_Cursor
|
||
Deallocate tbl_Cursor
|
||
|
||
lblExit:
|
||
Set NoCount Off
|
||
Drop Table #tblTmp
|
||
Return @ErrorStatus
|
||
End
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
CREATE Proc sp_AddRCIncident
|
||
@sUserName nvarchar(200),
|
||
@sDescription nvarchar(1000),
|
||
@iIndex int OUTPUT,
|
||
@iError int OUTPUT
|
||
As
|
||
Insert TblRCIncidents
|
||
(sUserName, sDescription)
|
||
Values
|
||
(@sUserName, @sDescription);
|
||
|
||
Select @iIndex = max (iIncidentID) from TblRCIncidents;
|
||
Set @iError = @@ERROR;
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|
||
CREATE Proc sp_UpdateRCIncident
|
||
@sFile nvarchar (1000),
|
||
@iIndex int,
|
||
@iError int OUTPUT
|
||
As
|
||
Update TblRCIncidents
|
||
Set sFile = @sFile
|
||
Where iIncidentID = @iIndex;
|
||
|
||
Set @iError = @@ERROR;
|
||
|
||
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
|
||
GO
|
||
|