November 15, 2010 at 1:32 pm
Ok I've got a massive search query it's got about 20 tables with between 10,000 and 500,000 records per table.
Because just about all of the parameters are optional, I went with a catch-all type query.
I've implemented Gail's ideas from this SQL in the Wild Blod post
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
And it's worked well for just about everything. I even extended it since I have SQL 2K8 to have table parameters in my stored proc for multi-valued searches (show me all products of product family X, or Product family Y, Product Group Z) by testing if there were any records in the @table parameter and if so, using an EXISTS predicate to see if the defects table hits a match in the table parameter or joining to it if necessary.
Except for handling a keyword search.
Users can enter none to many keywords which need to search across a product defect database both against a summary and a details column. In all cases they should get one row back per defect reported even if the keyword search has multiple hits.
I keep running into the brick wall that they expect the equivalent of LIKE '%value%' to be performed regardless of the performance hit, but it has to be "fast" as well.
I'm looking for something that performs better than this... because this forces time-outs, but I'm stumped at present as to what could be better with the horrible constraints I'm stuck with.
For instance a search on red, should return REDding, REDmon, RED, bRED, and boRED.
Right now going down the dynamic path I've got this.
Declare @test-2 int
SET @test-2 = 0
SELECT
@test-2=COUNT(*)
FROM
@Keywords
IF @test-2 > 0
BEGIN
CREATE TABLE #KeyWords(
KWID INT IDENTITY(1,1) PRIMARY KEY,
KeyWord VARCHAR(80))
INSERT #KeyWords(KeyWord)
SELECT
*
FROM @Keywords
CREATE INDEX #IDX_4 ON #KeyWords(KeyWord)
SET @WHERE = @WHERE + N' and
EXISTS (SELECT 1
FROM #KeyWords KW
WHERE pmr.pmrsummary LIKE ''%'' + KW.Keyword + ''%''
UNION ALL
SELECT 1
FROM #KeyWords KW2
WHERE pmr.pmrdescription LIKE ''%'' + KW2.Keyword + ''%''
)
'
END
I've tried joining the table, and instead of the UNION combining both searches with OR.
Here's the DDL
CREATE DATABASE CSS
GO
USE [CSS]
GO
CREATE TYPE PlatformProductSubproduct AS TABLE(
PMRPlatform VARCHAR(20),
PMRProduct VARCHAR(20),
PMRSubProduct VARCHAR(20)
)
CREATE TYPE PMGStatus AS TABLE(
PMGStatusID typ_PMG_projectStatus)
CREATE TYPE PMRStatus AS TABLE(
PMRStatusID INT )
CREATE TYPE ProjectManagament AS TABLE(
AreaID INT,
TeamID INT,
ReleaseID INT,
BuildID INT,
PRIORITY VARCHAR(20),
Score VARCHAR(6)
)
CREATE TYPE KeyWords AS TABLE(
KeyWord varchar(80)
)
CREATE TYPE PMRPriorities AS TABLE(
PriorityDESC VARCHAR(30),
SeverityDESC VARCHAR(30),
SubTypesDESC VARCHAR(30)
)
GO
/****** Object: Table [dbo].[tbl_pmr_ProManRelease] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_ProManRelease](
[ReleaseID] [int] IDENTITY(1,1) NOT NULL,
[ReleaseDesc] [varchar](30) NOT NULL,
[Inactive] [bit] NOT NULL,
[ChangedTimeStamp] [datetime] NOT NULL,
[UserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ReleaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IDX_pmr_promanrelease_test] ON [tbl_pmr_ProManRelease]
(
[ReleaseID] ASC,
[ReleaseDesc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_pmr_ProManBuild] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_ProManBuild](
[BuildID] [int] IDENTITY(1,1) NOT NULL,
[BuildDesc] [varchar](30) NOT NULL,
[Inactive] [bit] NOT NULL,
[ChangedTimeStamp] [datetime] NOT NULL,
[UserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[BuildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_SubTypes] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_SubTypes](
[SubTypeID] [int] IDENTITY(1,1) NOT NULL,
[TypeID] [int] NOT NULL,
[Desc] [varchar](30) NOT NULL,
[ORDER] [int] NOT NULL,
[Email] [tinyint] NOT NULL,
[LastChangeTS] [datetime] NOT NULL,
[LastChangeUserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[SubTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_Severity] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_Severity](
[SeverityID] [int] IDENTITY(1,1) NOT NULL,
[SubTypeID] [int] NOT NULL,
[Desc] [varchar](30) NOT NULL,
[ORDER] [int] NOT NULL,
[PMGPrioityID] [int] NOT NULL,
[SLA] [tinyint] NOT NULL,
[Email] [tinyint] NOT NULL,
[LastChangeTS] [datetime] NOT NULL,
[LastChangeUserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[SeverityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_Priorites] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_Priorites](
[PriorityID] [int] IDENTITY(1,1) NOT NULL,
[SeverityID] [int] NOT NULL,
[Desc] [varchar](30) NOT NULL,
[ORDER] [int] NOT NULL,
[Email] [tinyint] NOT NULL,
[LastChangeTS] [datetime] NOT NULL,
[LastChangeUserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PriorityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_PMRs] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_PMRs](
[PMRNumber] [int] IDENTITY(1,1) NOT NULL,
[PMRSupervisor] [varchar](8) NULL,
[PMRSubmitter] [varchar](8) NOT NULL,
[PMRAddedBy] [varchar](8) NOT NULL,
[PMRInHouse] [varchar](8) NOT NULL,
[PMRClientRes] [varchar](10) NULL,
[PMRDepartment] [varchar](6) NULL,
[PMRProductManager] [varchar](8) NULL,
[PMRManufactureVendor] [varchar](3) NULL,
[PMRProject] [varchar](10) NULL,
[PMRProductReview] [datetime] NULL,
[PMRSummary] [varchar](50) NOT NULL,
[PMRDescription] [varchar](max) NULL,
[PMRTestingFlag] [varchar](1) NOT NULL,
[PMRPrintedFlag] [varchar](1) NULL,
[PMRPilot] [char](1) NULL,
[PMRInReviewID] [char](8) NULL,
[PMRAddedTS] [datetime] NULL,
[PMRStatusChangeTS] [datetime] NULL,
[PMRCountry] [varchar](20) NULL,
[PMRCompletedTS] [datetime] NULL,
[PMRProgram] [varchar](20) NULL,
[PMRPlatform] [varchar](20) NULL,
[PMRProduct] [varchar](20) NULL,
[PMRSubProduct] [varchar](20) NULL,
[PMRDevCtr] [varchar](50) NULL,
[PMRTotalClients] [int] NULL,
[PMRTotalImpacted] [int] NULL,
[PMRTotalSeats] [int] NULL,
[PMRAttr] [varchar](1) NULL,
[PMRCorp] [varchar](1) NULL,
[SLA] [varchar](1) NOT NULL,
[PriorityID] [int] NOT NULL,
CONSTRAINT [PK_tbl_PMRs] PRIMARY KEY CLUSTERED
(
[PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [idx_PMR_AddedBy] ON [tbl_pmr_PMRs]
(
[PMRAddedBy] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_AddedTS] ON [tbl_pmr_PMRs]
(
[PMRAddedTS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_ClientRes] ON [tbl_pmr_PMRs]
(
[PMRClientRes] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_Department] ON [tbl_pmr_PMRs]
(
[PMRDepartment] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_InHouse] ON [tbl_pmr_PMRs]
(
[PMRInHouse] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_InReviewID] ON [tbl_pmr_PMRs]
(
[PMRInReviewID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_pmr_platform] ON [tbl_pmr_PMRs]
(
[PMRPlatform] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_PrintedFlag] ON [tbl_pmr_PMRs]
(
[PMRPrintedFlag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_pmr_product] ON [tbl_pmr_PMRs]
(
[PMRProduct] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_ProductManager] ON [tbl_pmr_PMRs]
(
[PMRProductManager] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_Project] ON [tbl_pmr_PMRs]
(
[PMRProject] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_ProjectRelease] ON [tbl_pmr_PMRs]
(
[PMRProductReview] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_StatusChangeTS] ON [tbl_pmr_PMRs]
(
[PMRStatusChangeTS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_Submitter] ON [tbl_pmr_PMRs]
(
[PMRSubmitter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_Summary] ON [tbl_pmr_PMRs]
(
[PMRSummary] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_Supervisor] ON [tbl_pmr_PMRs]
(
[PMRSupervisor] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMR_TestingFlag] ON [tbl_pmr_PMRs]
(
[PMRTestingFlag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_tbl_pmr_pmrs_platform] ON [tbl_pmr_PMRs]
(
[PMRPlatform] ASC,
[PMRProduct] ASC,
[PMRSubProduct] ASC,
[PMRNumber] ASC,
[PriorityID] ASC
)
INCLUDE ( [PMRSupervisor],
[PMRSubmitter],
[PMRClientRes],
[PMRProductManager],
[PMRProject],
[PMRSummary],
[PMRTestingFlag],
[PMRInReviewID],
[PMRAddedTS],
[PMRStatusChangeTS],
[PMRProgram],
[PMRDevCtr],
[PMRTotalClients],
[PMRTotalImpacted],
[PMRTotalSeats],
[PMRAttr],
[PMRCorp],
[SLA]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_tbl_pmr_projects] ON [tbl_pmr_PMRs]
(
[PMRNumber] ASC,
[PMRProject] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_tbl_pmr_projects_reverse] ON [tbl_pmr_PMRs]
(
[PMRProject] ASC,
[PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_PMG_Project] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_PMG_Project](
[ID] [dbo].[typ_PMG_projectID] IDENTITY(1,1) NOT NULL,
[LegacyID] [varchar](10) NULL,
[StatusID] [dbo].[typ_PMG_projectStatus] NOT NULL,
[PriorityID] [dbo].[typ_PMG_projectPriority] NOT NULL,
[CategoryID] [dbo].[typ_PMG_projectCategory] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Product] [varchar](20) NULL,
[SubProduct] [varchar](20) NULL,
[ProjectLeader] [dbo].[typ_PMG_userid] NULL,
[Supervisor] [dbo].[typ_PMG_userid] NULL,
[ProductManager] [dbo].[typ_PMG_userid] NULL,
[ScheduledStart] [datetime] NULL,
[ActualStart] [datetime] NULL,
[ScheduledFinish] [datetime] NULL,
[ActualFinish] [datetime] NULL,
[Release] [dbo].[typ_PMG_version] NULL,
[NPDProposedDate] [datetime] NULL,
[NPDApprovedDate] [datetime] NULL,
[Miscellaneous1] [varchar](25) NULL,
[Miscellaneous2] [varchar](25) NULL,
[Miscellaneous3] [varchar](25) NULL,
[GroupMoveOnly] [bit] NOT NULL,
[Rank] [int] NULL,
[CreateUserid] [dbo].[typ_PMG_userid] NOT NULL,
[CreateTimestamp] [datetime] NOT NULL,
[LastChangeUserid] [dbo].[typ_PMG_userid] NOT NULL,
[LastChangeTimestamp] [datetime] NOT NULL,
[Description] [text] NULL,
[Platform] [varchar](20) NULL,
[ETC] [int] NULL,
[LastStatusChangeTimestamp] [datetime] NULL,
[LastStatusChangeUserid] [char](8) NULL,
[DevCenter] [varchar](50) NULL,
[Solution] [text] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [idx_PMG_Project_Search] UNIQUE NONCLUSTERED
(
[ID] ASC,
[LegacyID] ASC,
[StatusID] ASC,
[PriorityID] ASC,
[CategoryID] ASC,
[Name] ASC,
[Product] ASC,
[ProjectLeader] ASC,
[Supervisor] ASC,
[ProductManager] ASC,
[Release] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ActualFinish] ON [tbl_PMG_Project]
(
[ActualFinish] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ActualStart] ON [tbl_PMG_Project]
(
[ActualStart] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_CategoryID] ON [tbl_PMG_Project]
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_CreatedTimestamp] ON [tbl_PMG_Project]
(
[CreateTimestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_CreatedUserid] ON [tbl_PMG_Project]
(
[CreateUserid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_DevCenter] ON [tbl_PMG_Project]
(
[DevCenter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_LastChangeTimestamp] ON [tbl_PMG_Project]
(
[LastChangeTimestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_LastChangeUserid] ON [tbl_PMG_Project]
(
[LastChangeUserid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_LastStatusChangeTimestamp] ON [tbl_PMG_Project]
(
[LastStatusChangeTimestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_LastStatusChangeUserid] ON [tbl_PMG_Project]
(
[LastStatusChangeUserid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_LegacyID] ON [tbl_PMG_Project]
(
[LegacyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Miscellaneous1] ON [tbl_PMG_Project]
(
[Miscellaneous1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Miscellaneous2] ON [tbl_PMG_Project]
(
[Miscellaneous2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Miscellaneous3] ON [tbl_PMG_Project]
(
[Miscellaneous3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Name] ON [tbl_PMG_Project]
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_NpdApprovedDate] ON [tbl_PMG_Project]
(
[NPDApprovedDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_NpdProposedDate] ON [tbl_PMG_Project]
(
[NPDProposedDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Platform] ON [tbl_PMG_Project]
(
[Platform] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_PriorityID] ON [tbl_PMG_Project]
(
[PriorityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Product] ON [tbl_PMG_Project]
(
[Product] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ProductManager] ON [tbl_PMG_Project]
(
[ProductManager] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ProjectLeader] ON [tbl_PMG_Project]
(
[ProjectLeader] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Release] ON [tbl_PMG_Project]
(
[Release] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ScheduledFinish] ON [tbl_PMG_Project]
(
[ScheduledFinish] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_ScheduledStart] ON [tbl_PMG_Project]
(
[ScheduledStart] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_StatusID] ON [tbl_PMG_Project]
(
[StatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_SubProduct] ON [tbl_PMG_Project]
(
[SubProduct] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_PMG_Projects_Supervisor] ON [tbl_PMG_Project]
(
[Supervisor] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_pmr_Clients] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_Clients](
[PMRC_ID] [int] IDENTITY(1,1) NOT NULL,
[PMRC_PMRNumber] [int] NOT NULL,
[PMRC_Agn_Seq] [int] NOT NULL,
[PMRC_Seats_Impacted] [int] NOT NULL,
[PMRC_Added] [datetime] NULL,
[PMRC_Submitter] [varchar](8) NOT NULL,
[PMRC_Contact] [varchar](35) NULL,
[PMRC_Contact_Phone] [varchar](20) NULL,
[PMRC_Version] [char](8) NULL,
[PMRC_Notes] [varchar](75) NULL,
[PMRC_Delete] [int] NOT NULL,
[PMRC_SLA] [bit] NOT NULL,
[PMRC_Product] [varchar](45) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PMRC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IDX_pmr_clients_test] ON [tbl_pmr_Clients]
(
[PMRC_SLA] ASC,
[PMRC_PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_SLA] ON [tbl_pmr_Clients]
(
[PMRC_SLA] ASC,
[PMRC_PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Trigger [tr_Update_Client_onto_PMG_SLA] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
Procedure: tr_Update_Client_onto_PMG_SLA
Author: CPetersen
Purpose: If a Client is Updated to or from SLA it checks to see if the
PMR is on PMG then updates the Priority of the PMG if it has to.
Creation Date: 11/16/2009
Modifications:
**********************************************************************/
CREATE TRIGGER [tr_Update_Client_onto_PMG_SLA]
ON [tbl_pmr_Clients]
FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PMR INT
DECLARE @oldSLA INT
DECLARE @newSLA INT
DECLARE CSR_BOB Cursor FORWARD_ONLY for
SELECT
DISTINCT
a.PMRC_PMRNUMBER
FROM INSERTED a JOIN DELETED B ON
a.PMRC_ID = b.PMRC_ID
WHERE
a.PMRC_SLA != b.PMRC_SLA
OPEN CSR_BOB
FETCH NEXT FROM CSR_BOB INTO @PMR
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC up_PMG_Update_SLA_Client_Change @PMR
FETCH NEXT FROM CSR_BOB INTO @PMR
END
CLOSE CSR_BOB
DEALLOCATE CSR_BOB
END
GO
/****** Object: Trigger [tr_Delete_Client_onto_PMG_SLA] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
Procedure: tr_Delete_Client_onto_PMG_SLA
Author: CPetersen
Purpose: If a Client is Updated to or from SLA it checks to see if the
PMR is on PMG then updates the Priority of the PMG if it has to.
Creation Date: 11/16/2009
Modifications:
**********************************************************************/
CREATE TRIGGER [tr_Delete_Client_onto_PMG_SLA]
ON [tbl_pmr_Clients]
FOR DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PMR INT
DECLARE @SLA INT
SELECT @SLA = PMRC_SLA,
@PMR = PMRC_PMRNumber
FROM DELETED
IF (@SLA != 0)
BEGIN
EXEC up_PMG_Update_SLA_Client_Change @PMR
END
END
GO
/****** Object: Trigger [tbl_pmr_clients_iu] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [tbl_pmr_clients_iu] ON [tbl_pmr_Clients]
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @pmrnumber int
DECLARE @slaval varchar(1)
SELECT @pmrnumber = pmrc_pmrNumber,
@slaval = case cast(pmrc_sla as int) when 0 then 'N' else 'Y' end
FROM inserted
EXEC usp_PMR_SLA_TEST @pmrnumber,@slaval
END
GO
/****** Object: Trigger [tbl_pmr_clients_d] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [tbl_pmr_clients_d] ON [tbl_pmr_Clients]
FOR DELETE
AS
BEGIN
DECLARE @pmrnumber int
DECLARE @slaval varchar(1)
SELECT @pmrnumber = pmrc_pmrNumber,
@slaval = case cast(pmrc_sla as int) when 0 then 'N' else 'Y' end
FROM deleted
EXEC usp_PMR_SLA_TEST @pmrnumber,@slaval
END
GO
/****** Object: Table [dbo].[tbl_pmr_Status] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_Status](
[StatusID] [int] IDENTITY(1,1) NOT NULL,
[StatusName] [varchar](50) NOT NULL,
[StatusType] [char](1) NOT NULL,
[AddedUserID] [char](8) NOT NULL,
[AddedTimestamp] [datetime] NOT NULL,
[LCUserID] [char](8) NOT NULL,
[LCTimestamp] [datetime] NOT NULL,
[AggregateStatus] [varchar](20) NULL,
CONSTRAINT [PK_tbl_pmr_Status] PRIMARY KEY CLUSTERED
(
[StatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Trigger [tr_Insert_Client_onto_PMG_SLA] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
Procedure: tr_Insert_Client_onto_PMG_SLA
Author: CPetersen
Purpose: If an SLA Client is added it checks to see if the PMR is on PMG
then updates the Priority of the PMG if it has to.
Creation Date: 11/16/2009
Modifications:
**********************************************************************/
CREATE TRIGGER [tr_Insert_Client_onto_PMG_SLA]
ON [tbl_pmr_Clients]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PMG INT
DECLARE @PMR INT
DECLARE @SLA INT
SELECT
@PMR = PMRC_PMRNumber,
@SLA = PMRC_SLA
FROM
INSERTED
IF (@SLA = 1)
BEGIN
SELECT @PMG = ProjectID
FROM tbl_pmg_ProjectPmr
WHERE Number = @PMR
IF @PMG is NOT null
BEGIN
UPDATE tbl_PMG_Project
SET PriorityID = 4
WHERE ID = @PMG
END
END
END
GO
/****** Object: Table [dbo].[tbl_pmr_ReleaseNotes] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_ReleaseNotes](
[PMRNumber] [int] NOT NULL,
[IntNote] [varchar](max) NULL,
[ExtNote] [varchar](max) NULL,
[Checked] [int] NOT NULL,
[UserID] [varchar](8) NOT NULL,
[NoteTimestamp] [datetime] NOT NULL,
[TestNote] [varchar](max) NULL,
[FrenchNote] [varchar](max) NULL,
[CheckUS] [int] NOT NULL,
[CheckCanada] [int] NOT NULL,
[CheckFrench] [int] NOT NULL,
[CheckUK] [int] NOT NULL,
CONSTRAINT [PK_TBLPMR_RELEASENOTES] PRIMARY KEY CLUSTERED
(
[PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_ProManReleaseBuilds] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_ProManReleaseBuilds](
[PMRNumber] [int] NOT NULL,
[ReleaseID] [int] NULL,
[Priority] [varchar](20) NULL,
[BuildID] [int] NULL,
[ChangedTimeStamp] [datetime] NOT NULL,
[UserID] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PMRNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_pmr_PmrMarkets] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tbl_pmr_PmrMarkets](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PmrNumber] [int] NOT NULL,
[Market] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IDX_pmrmarkets] ON [tbl_pmr_PmrMarkets]
(
[PmrNumber] ASC,
[Market] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tbl_pmr_PmrMarkets] ON [tbl_pmr_PmrMarkets]
(
[PmrNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tbl_pmr_Audit] Script Date: 11/15/2010 14:21:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [tbl_pmr_Audit](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PmrNumber] [int] NOT NULL,
[UserID] [nchar](8) NOT NULL,
[Timestamp] [datetime] NOT NULL,
[Type] [smallint] NOT NULL,
[Field] [nvarchar](50) NULL,
[From] [nvarchar](70) NULL,
[To] [nvarchar](70) NULL,
CONSTRAINT [PK_tbl_pmr_audit] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_pmr_audit_1] ON [tbl_pmr_Audit]
(
[PmrNumber] ASC,
[UserID] ASC,
[Timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_pmr_audit_UserID] ON [tbl_pmr_Audit]
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_tbl_pmr_audit_2] ON [tbl_pmr_Audit]
(
[Timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Default [DF__tbl_PMG_P__Group__1790AA01] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_PMG_Project] ADD CONSTRAINT [DF__tbl_PMG_P__Group__1790AA01] DEFAULT (0) FOR [GroupMoveOnly]
GO
/****** Object: Default [DF__tbl_pmr_A__Times__50633D86] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Audit] ADD CONSTRAINT [DF__tbl_pmr_A__Times__50633D86] DEFAULT (getutcdate()) FOR [Timestamp]
GO
/****** Object: Default [DF__tbl_pmr_c__PMRC___1F7B1935] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Clients] ADD DEFAULT (0) FOR [PMRC_SLA]
GO
/****** Object: Default [DF__tbl_pmr_c__PMRC___206F3D6E] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Clients] ADD DEFAULT ('General') FOR [PMRC_Product]
GO
/****** Object: Default [DF__tbl_pmr_pmr__SLA__2BE0F01A] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_PMRs] ADD DEFAULT ('N') FOR [SLA]
GO
/****** Object: Default [DF__tbl_pmr_P__Inact__691F0884] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManBuild] ADD DEFAULT (0) FOR [Inactive]
GO
/****** Object: Default [DF__tbl_pmr_P__Inact__739C96F7] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManRelease] ADD DEFAULT (0) FOR [Inactive]
GO
/****** Object: ForeignKey [FK_tbl_PMG_Project_tbl_PMG_Priorities] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_PMG_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_Priorities] FOREIGN KEY([PriorityID])
REFERENCES [tbl_PMG_Priorities] ([ID])
GO
ALTER TABLE [tbl_PMG_Project] CHECK CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_Priorities]
GO
/****** Object: ForeignKey [FK_tbl_PMG_Project_tbl_PMG_ProjectCategories] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_PMG_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_ProjectCategories] FOREIGN KEY([CategoryID])
REFERENCES [tbl_PMG_ProjectCategories] ([ID])
GO
ALTER TABLE [tbl_PMG_Project] CHECK CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_ProjectCategories]
GO
/****** Object: ForeignKey [FK_tbl_PMG_Project_tbl_PMG_ProjectStatuses] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_PMG_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_ProjectStatuses] FOREIGN KEY([StatusID])
REFERENCES [tbl_PMG_ProjectStatuses] ([ID])
GO
ALTER TABLE [tbl_PMG_Project] CHECK CONSTRAINT [FK_tbl_PMG_Project_tbl_PMG_ProjectStatuses]
GO
/****** Object: ForeignKey [fk_AuditPMRs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Audit] WITH CHECK ADD CONSTRAINT [fk_AuditPMRs] FOREIGN KEY([PmrNumber])
REFERENCES [tbl_pmr_PMRs] ([PMRNumber])
ON DELETE CASCADE
GO
ALTER TABLE [tbl_pmr_Audit] CHECK CONSTRAINT [fk_AuditPMRs]
GO
/****** Object: ForeignKey [fk_PMRAuditTypes] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Audit] WITH CHECK ADD CONSTRAINT [fk_PMRAuditTypes] FOREIGN KEY([Type])
REFERENCES [tbl_pmr_AuditTypes] ([ID])
GO
ALTER TABLE [tbl_pmr_Audit] CHECK CONSTRAINT [fk_PMRAuditTypes]
GO
/****** Object: ForeignKey [fk_ClientSubmitters] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Clients] WITH CHECK ADD CONSTRAINT [fk_ClientSubmitters] FOREIGN KEY([PMRC_Submitter])
REFERENCES [tbl_UCSEmployee] ([EmailUserID])
ON UPDATE CASCADE
GO
ALTER TABLE [tbl_pmr_Clients] CHECK CONSTRAINT [fk_ClientSubmitters]
GO
/****** Object: ForeignKey [fk_PMRNumber] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Clients] WITH CHECK ADD CONSTRAINT [fk_PMRNumber] FOREIGN KEY([PMRC_PMRNumber])
REFERENCES [tbl_pmr_PMRs] ([PMRNumber])
ON DELETE CASCADE
GO
ALTER TABLE [tbl_pmr_Clients] CHECK CONSTRAINT [fk_PMRNumber]
GO
/****** Object: ForeignKey [fk_MKPMRs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_PmrMarkets] WITH CHECK ADD CONSTRAINT [fk_MKPMRs] FOREIGN KEY([PmrNumber])
REFERENCES [tbl_pmr_PMRs] ([PMRNumber])
ON DELETE CASCADE
GO
ALTER TABLE [tbl_pmr_PmrMarkets] CHECK CONSTRAINT [fk_MKPMRs]
GO
/****** Object: ForeignKey [fk_Priority] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_PMRs] WITH CHECK ADD CONSTRAINT [fk_Priority] FOREIGN KEY([PriorityID])
REFERENCES [tbl_pmr_Priorites] ([PriorityID])
GO
ALTER TABLE [tbl_pmr_PMRs] CHECK CONSTRAINT [fk_Priority]
GO
/****** Object: ForeignKey [FK__tbl_pmr_P__LastC__3B2333AA] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Priorites] WITH CHECK ADD FOREIGN KEY([SeverityID])
REFERENCES [tbl_pmr_Severity] ([SeverityID])
GO
/****** Object: ForeignKey [fk_PMBUserIDs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManBuild] WITH CHECK ADD CONSTRAINT [fk_PMBUserIDs] FOREIGN KEY([UserID])
REFERENCES [tbl_UCSEmployee] ([EmailUserID])
ON UPDATE CASCADE
GO
ALTER TABLE [tbl_pmr_ProManBuild] CHECK CONSTRAINT [fk_PMBUserIDs]
GO
/****** Object: ForeignKey [fk_PMPMRUserIDs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManRelease] WITH CHECK ADD CONSTRAINT [fk_PMPMRUserIDs] FOREIGN KEY([UserID])
REFERENCES [tbl_UCSEmployee] ([EmailUserID])
ON UPDATE CASCADE
GO
ALTER TABLE [tbl_pmr_ProManRelease] CHECK CONSTRAINT [fk_PMPMRUserIDs]
GO
/****** Object: ForeignKey [FK__tbl_pmr_P__Build__78614C14] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManReleaseBuilds] WITH CHECK ADD FOREIGN KEY([BuildID])
REFERENCES [tbl_pmr_ProManBuild] ([BuildID])
GO
/****** Object: ForeignKey [FK__tbl_pmr_P__Relea__776D27DB] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManReleaseBuilds] WITH CHECK ADD FOREIGN KEY([ReleaseID])
REFERENCES [tbl_pmr_ProManRelease] ([ReleaseID])
GO
/****** Object: ForeignKey [FK__tbl_pmr_P__UserI__767903A2] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManReleaseBuilds] WITH CHECK ADD FOREIGN KEY([PMRNumber])
REFERENCES [tbl_pmr_PMRs] ([PMRNumber])
ON DELETE CASCADE
GO
/****** Object: ForeignKey [fk_PMPMRBUserIDs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ProManReleaseBuilds] WITH CHECK ADD CONSTRAINT [fk_PMPMRBUserIDs] FOREIGN KEY([UserID])
REFERENCES [tbl_UCSEmployee] ([EmailUserID])
ON UPDATE CASCADE
GO
ALTER TABLE [tbl_pmr_ProManReleaseBuilds] CHECK CONSTRAINT [fk_PMPMRBUserIDs]
GO
/****** Object: ForeignKey [fk_RNPMRs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ReleaseNotes] WITH CHECK ADD CONSTRAINT [fk_RNPMRs] FOREIGN KEY([PMRNumber])
REFERENCES [tbl_pmr_PMRs] ([PMRNumber])
ON DELETE CASCADE
GO
ALTER TABLE [tbl_pmr_ReleaseNotes] CHECK CONSTRAINT [fk_RNPMRs]
GO
/****** Object: ForeignKey [fk_RNUserIDs] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_ReleaseNotes] WITH CHECK ADD CONSTRAINT [fk_RNUserIDs] FOREIGN KEY([UserID])
REFERENCES [tbl_UCSEmployee] ([EmailUserID])
ON UPDATE CASCADE
GO
ALTER TABLE [tbl_pmr_ReleaseNotes] CHECK CONSTRAINT [fk_RNUserIDs]
GO
/****** Object: ForeignKey [FK__tbl_pmr_S__LastC__3752A2C6] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Severity] WITH CHECK ADD FOREIGN KEY([SubTypeID])
REFERENCES [tbl_pmr_SubTypes] ([SubTypeID])
GO
/****** Object: ForeignKey [FK__tbl_pmr_S__PMGPr__3846C6FF] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_Severity] WITH CHECK ADD FOREIGN KEY([PMGPrioityID])
REFERENCES [tbl_PMG_Priorities] ([ID])
GO
/****** Object: ForeignKey [FK__tbl_pmr_S__LastC__3476361B] Script Date: 11/15/2010 14:21:15 ******/
ALTER TABLE [tbl_pmr_SubTypes] WITH CHECK ADD FOREIGN KEY([TypeID])
REFERENCES [tbl_pmr_Types] ([TypeID])
GO
here's the stored proc in it's fullness.
USE [CSS]
GO
/****** Object: StoredProcedure [up_pmr_SelectPMRsByPageWhereArgument] Script Date: 11/15/2010 11:22:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************
Procedure: up_pmr_SelectPMRsByPageWhereArgument
Author: Unknown
Purpose: Unknown
Creation Date: Unknown
Modifications:
6/1/2009 Mark Tassin Added set Transacton isolation level read
uncommitted to stop locking data when reading it
8/21/2009 CPetersen Changed the view in the from to a select statement.
Also added a join to tbl_pmr_ProManEstTime so that can search on these fields,
none of which are retruned so a distinct was also added to the select to try add
keep the pmrs from appearing more than once. In the select also added 3 new fields
from tbl_pmr_ProManReleaseBuilds that need to come back ProjManPriority,
Build.BuildDesc, Release.ReleaseDesc
11/12/2009 CPetersen - added the SLA
05/25/10 CPetersen - Modified to account for the changes to Type, SubType,
Severity, Priority
********************************************************************************/
ALTER Procedure [up_pmr_SelectPMRsByPageWhereArgument]
@nCurrentPage int = NULL, --0)
@nPageSize int = 10, --1)
@nvchSortField nvarchar(max) = NULL, --2)
@nvchAscDesc nvarchar(10) = 'DESC', --3)
--dbo.tbl_pmr_Clients
@iClient int = null, --4) PMRC_Agn_Seq
@sSubmitter nvarchar(8)=null, --5) PMRC_Submitter
--dbo.dbo.tbl_pmr_SubTypes
@TypeID int = null, --6) TypeID
--dbo.tbl_pmr_PMRs
@sAttr nvarchar(1)=null, --7) PMRAttr
@scorp nvarchar(1)=null, --8) PMRCorp
@iTotalClients int=null,--9) PMRTotalClients
@iTotalSeats int=null,--10) PMRTotalSeats
@iTotalImpacted int=null,--11) PMRTotalImpacted
@sClientRes nvarchar(10)=null,--12) PMRClientRes
@sProject nvarchar(10)=null,--13) PMRProject
@sSupervisor nvarchar(8)=null, --14) PMRSupervisor
@sProductManager nvarchar(8)=null, --15) PMRProductManager
@sInReviewID nvarchar(8)=null, --16) PMRInReviewID
@sProgram nvarchar(20)=null, --17) PMRProgram
@QATestPMRs nvarchar(1)=null,--18) PMRTestingFlag
@sSLAStatus nvarchar(1)=null,--19) SLA
--Dates added
@dtAddFrom Datetime=null, -->=20) PMRAddedTS
@dtAddTo Datetime=null, -->=21) PMRAddedTS
--Dates StatusChanged
@dtSCFrom Datetime=null, -->=22) PMRStatusChangeTS
@dtSCTo Datetime=null, -->=23) PMRStatusChangeTS
--Dates LastChange, COuld this one be done by adding a coloumn to the table and keeping it uptodate witha trigger?
--tbl_pmr_Audit
@dtLCFrom Datetime=null, --24) >=Timestamp
@dtLCTo Datetime=null, --25) >=Timestamp
--tbl_pmr_ReleaseNotes
@iUSChecked int=null, --26) CheckUS 1 or null (null from the table means 1)
@iCanadaChecked int=null, --27) CheckCanada 1 or null (null from the table means 1)
@iFrenchChecked int=null, --28) CheckFrench 1 or null (null from the table means 1)
@iUKChecked int=null, --29) CheckUK 1 or null (null from the table means 1)
@iUIChangesChecked int=null, --30) Checked
@iTestNoteBlank int=null, --31) if (TestNoteBlank == 1)
-- ((TestNote is null) or (TestNote like ''))
--if (TestNoteBlank == 2)
-- ((TestNote is not null) and not((TestNote like '')))
@iFrenchNoteBlank int=null, --32) if (FrenchNoteBlank == 1)
--((FrenchNote is null) or (FrenchNote like ''))
-- if (FrenchNoteBlank == 2)
-- ((FrenchNote is not null) and not((FrenchNote like '')))
@iExternalNoteBlank int=null, --33) if (ExternalNoteBlank == 1)
-- ((ExtNote is null) or (ExtNote like ''))
--if (ExternalNoteBlank == 2)
--((ExtNote is not null) and not((ExtNote like '')))
@iIntNote int=null, --34) if (IntNote == 1)
-- ((IntNote is null) or (IntNote like ''))
--if (IntNote == 2)
--((IntNote is not null) and not((IntNote like '')))
--tbl_pmr_Status
@sStatusType nvarchar(1)=null, --35) StatusType
--tbl_PMG_Project
@sPMGRelease nvarchar=null,--36) Release
--tbl_pmr_PmrMarkets
@Market nvarchar(20) = null, --37) Market
--Table Variables
@ProdTrio PlatformProductSubproduct READONLY, --38)
@p_oStatBC PMRStatus READONLY, --39)
@PMGstatBC PMGStatus READONLY, --40)
@ProjectManagement ProjectManagament READONLY, --41) IDs, Release, build, area, team, Strings ProjManPriority, Scoring (each field is anded, each record is or'd)
@Priority PMRPriorities READONLY, --42) abbrivations SubType DESC, Severity Desc, priorites Desc
@Keywords KeyWords READONLY --43) Seach both pmrsummary and description
--WITH RECOMPILE
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Mark Tassin 06/01/2009 Stop locking data when reading it
SET ANSI_NULLS OFF
DECLARE @SELECT nvarchar(MAX)
DECLARE @WHERE nvarchar(MAX)
DECLARE @ORDERBY nvarchar(MAX)
DECLARE @CMD NVARCHAR(MAX)
DECLARE @CMD2 NVARCHAR(MAX)
DECLARE @test-2 int
DECLARE @PMRCadded INT =0
DECLARE @AudLCAdded INT=0
DECLARE @nTotalRec INT
SET @npagesize = @npagesize +1
IF (@nvchSortField IS NULL)
BEGIN
SET @nvchSortField = N'pmr.PMRNumber ' + @nvchAscDesc
END
IF (@nvchSortField = 'ProductAndSubProduct')
BEGIN
SET @nvchSortField = N' pmr.PMRProduct ' + @nvchAscDesc + N', pmr.PMRSubProduct ' + @nvchAscDesc
END
IF @nvchSortField NOT LIKE '%pmr.PMRNumber%'
BEGIN
SET @nvchSortField = @nvchSortField + ' ' + @nvchAscDesc + ',pmr.PMRNumber ' + @nvchAscDesc
END
ELSE
BEGIN
IF RTRIM(LTRIM(RIGHT(@nvchSortField,4))) NOT IN ('ASC','DESC')
SET @nvchSortField = @nvchSortField + N' ' + @nvchAscDesc
END
SELECT @SELECT = N'
;WITH Search_CTE AS (
SELECT
pmr.PMRNumber,
Submitter=pmr.PMRSubmitter,
Summary=pmr.PMRSummary,
Testing=pmr.PMRTestingFlag,
AddedTS=pmr.PMRAddedTS,
StatusChangeTS = pmr.PMRStatusChangeTS,
s.StatusID,
s.StatusName,
s.StatusType,
ClientRes=pmr.PMRClientRes,
Project=pmr.PMRProject,
Supervisor=pmr.PMRSupervisor,
ProductMgr=pmr.PMRProductManager,
InReviewID=pmr.PMRInReviewID,
Program=pmr.PMRProgram,
PlatformName=pmr.PMRPlatform,
ProductName=pmr.PMRProduct,
SubProductName=pmr.PMRSubProduct,
pmr.PMRDevCtr,
Release=proj.Release,
proj.StatusID AS PMGStatus,
Market=market.Market,
pmr.PMRTotalClients,
pmr.PMRCorp,
pmr.PMRAttr,
pmr.PMRTotalSeats,
pmr.PMRTotalImpacted,
ProjManPriority=PMRB.Priority,
Build.BuildDesc,
Release.ReleaseDesc,
Build.BuildID,
Release.ReleaseID,
SLA=pmr.SLA,
PriorityName=PR.[Desc],
SeverityName=SR.[Desc],
SubTypeName=ST.[Desc],
ST.TypeID,
recordnum = ROW_NUMBER() OVER (ORDER BY ' + @nvchSortField + N' ),
pagenum = CASE @nPageSize WHEN 0 THEN 0 ELSE 1 + ROW_NUMBER() OVER (ORDER BY ' + @nvchSortField + N' ) / @nPageSize END
FROM
tbl_pmr_PMRs pmr join tbl_pmr_Priorites PR ON
pmr.PriorityID = PR.PriorityID
join tbl_pmr_Severity SR ON
PR.SeverityID = SR.SeverityID
join tbl_pmr_SubTypes ST ON
SR.SubTypeID = ST.SubTypeID
left outer join tbl_pmr_PMRStatus ps ON
pmr.PMRNumber = ps.PMRNumber
inner join tbl_pmr_Status s ON
ps.CurrentStatus = s.StatusID
left outer join tbl_pmr_ReleaseNotes rn ON
pmr.PMRNumber = rn.PMRNumber
left outer join tbl_PMG_Project proj ON
pmr.PMRProject = proj.ID
left outer join tbl_pmr_PmrMarkets market ON
pmr.PMRNumber = market.PmrNumber
left outer join tbl_pmr_ProManReleaseBuilds PMRB ON
pmr.PMRNumber = PMRB.PMRNumber
left outer join tbl_pmr_ProManBuild Build ON
Build.BuildID = PMRB.BuildID
left outer join tbl_pmr_ProManRelease Release ON
PMRB.ReleaseID = Release.ReleaseID
'
--PRINT @SELECT
SET @WHERE = N' WHERE 1=1 '
SELECT
@test-2 = COUNT(*)
FROM
@ProdTrio
IF @test-2 > 0
BEGIN
CREATE TABLE #ProdTrio(
PTID INT IDENTITY(1,1) PRIMARY KEY,
PMRPlatform VARCHAR(20),
PMRProduct varchar(20),
PMRSubProduct VARCHAR(20))
INSERT #ProdTrio(PMRPlatform,PMRProduct,PMRSubProduct)
SELECT
*
FROM @ProdTrio
CREATE INDEX #IDX_1 ON #ProdTrio(PMRPlatform,PMRProduct,PMRSubProduct)
CREATE INDEX #IDX_2 ON #ProdTrio(PMRPlatform,PMRProduct)
CREATE INDEX #IDX_3 ON #ProdTrio(PMRPlatform)
SET @WHERE = @WHERE + '
AND ( EXISTS (SELECT 1 FROM #ProdTrio filter1 WHERE filter1.PMRPlatform=pmr.PMRPlatform and filter1.PMRPRoduct IS NULL and filter1.PMRSubProduct IS NULL)
OR EXISTS (SELECT 1 FROM #ProdTrio filter1b WHERE filter1b.PMRPlatform=pmr.PMRPlatform and filter1b.PMRProduct = pmr.PMRPRoduct AND filter1b.PMRSubProduct IS NULL)
OR EXISTS (SELECT 1 FROM #PRodTrio filter1c WHERE filter1c.PMRPlatform=pmr.PMRPlatform and filter1c.PMRProduct = pmr.PMRPRoduct AND filter1c.PMRSubProduct = pmr.PMRSubProduct)
)'
END
SET @test-2 = 0
SELECT
@test-2 = COUNT(*)
FROM
@p_oStatBC
IF @test-2 > 0
BEGIN
SET @SELECT = @SELECT + N'
JOIN @p_oStatBC filter2 ON
ps.CurrentStatus = filter2.PMRStatusID
'
END
SET @test-2 = 0
SELECT
@test-2 = COUNT(*)
FROM
@PMGstatBC
IF @test-2 > 0
BEGIN
SET @SELECT = @SELECT + N'
JOIN @PMGstatBC filter3 ON
proj.StatusID = filter3.PMGStatusID
'
END
SET @test-2 = 0
SELECT
@test-2 = COUNT(*)
FROM
@ProjectManagement
IF @test-2 > 0
BEGIN
SET @WHERE = @WHERE + N' AND
EXISTS (SELECT 1
FROM tbl_pmr_ProManReleaseBuilds RB full outer join tbl_pmr_ProManEstTime ET
ON RB.PMRNumber = ET.PMRNumber JOIN @ProjectManagement filter4 ON
ISNULL(RB.ReleaseID,0)=ISNULL(ISNULL(filter4.ReleaseID,RB.ReleaseID),0) AND
ISNULL(ET.AreaID,0) = ISNULL(ISNULL(Filter4.AreaID,ET.AreaID),0) AND
ISNULL(ET.TeamID,0) = ISNULL(ISNULL(Filter4.TeamID,ET.TeamID),0) AND
ISNULL(RB.BuildID,0) = ISNULL(ISNULL(Filter4.BuildID,RB.BuildID),0) AND
ISNULL(RB.Priority,0) = ISNULL(ISNULL(Filter4.Priority,RB.Priority),0) AND
ISNULL(ET.Scoring,0) = ISNULL(ISNULL(filter4.Score,ET.Scoring),0)
WHERE ((pMr.PMRNumber = rb.PMRNumber) or (pmr.PMRNumber = et.PMRNumber)) )
'
END
SET @test-2 = 0
SELECT
@test-2 = COUNT(*)
FROM
@Priority
IF @test-2 > 0
BEGIN
SET @SELECT = @SELECT + N'
JOIN @Priority filter5 ON
PR.[Desc] = ISNULL(filter5.PriorityDesc,PR.[Desc]) AND
SR.[Desc] = ISNULL(filter5.SeverityDesc,SR.[Desc]) AND
ST.[Desc] = ISNULL(filter5.SubTypesDesc,ST.[Desc])
'
END
SET @test-2 = 0
SELECT
@test-2=COUNT(*)
FROM
@Keywords
IF @test-2 > 0
BEGIN
CREATE TABLE #KeyWords(
KWID INT IDENTITY(1,1) PRIMARY KEY,
KeyWord VARCHAR(80))
INSERT #KeyWords(KeyWord)
SELECT
*
FROM @Keywords
CREATE INDEX #IDX_4 ON #KeyWords(KeyWord)
SET @WHERE = @WHERE + N' and
EXISTS (SELECT 1
FROM #KeyWords KW
WHERE pmr.pmrsummary LIKE ''%'' + KW.Keyword + ''%''
UNION ALL
SELECT 1
FROM #KeyWords KW2
WHERE pmr.pmrdescription LIKE ''%'' + KW2.Keyword + ''%''
)
'
END
--WHERE ((pmr.PMRSummary like ''%'' + KW.KeyWord +''%'') or (pmr.PMRDescription like ''%'' + KW.KeyWord +''%'')))
/********* tbl_pmr_clients *******************/
IF @iClient IS NOT NULL
BEGIN
IF @PMRCAdded != 1
BEGIN
SET @SELECT = @SELECT + N'
LEFT JOIN tbl_pmr_clients clients ON
pmr.PMRNumber = clients.PMRC_PMRNumber
'
SET @PMRCAdded = 1
END
SET @WHERE = @WHERE + N' AND
clients.PMRC_Agn_Seq = @iClient '
END
--PRINT @SELECT
IF @sSubmitter IS NOT NULL
BEGIN
IF @PMRCAdded != 1
BEGIN
SET @SELECT = @SELECT + N'
LEFT JOIN tbl_pmr_clients clients ON
pmr.PMRNumber = clients.PMRC_PMRNumber '
SET @PMRCAdded = 1
END
SET @WHERE = @WHERE + N' AND
clients.PMRC_Submitter = @sSubmitter '
END
--dbo.dbo.tbl_pmr_SubTypes
if @TypeID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N' AND
ST.TypeID = @TypeID '
END
--PRINT @SELECT
/****** tbl_pmr_pmrs *******/
IF @sAttr IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRAttr = @sAttr
'
END
IF @scorp IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRCorp = @scorp
'
END
IF @iTotalClients IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRTotalClients >= @iTotalClients
'
END
IF @iTotalSeats IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRTotalSeats >= @iTotalSeats
'
END
IF @iTotalImpacted IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRTotalImpacted >= @iTotalImpacted
'
END
IF @sClientRes IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRClientRes = @sClientRes
'
END
IF @sProject IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRProject = @sProject
'
END
IF @sSupervisor IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRSupervisor = @sSupervisor
'
END
IF @sProductManager IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRProductManager = @sProductManager
'
END
IF @sInReviewID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRInReviewID = @SInReviewID
'
END
IF @sProgram IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRProgram = @sProgram
'
END
IF @QATestPMRs IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRTestingFlag = @QATestPMRs
'
END
IF @sSLAStatus IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.SLA = @sSLAStatus
'
END
IF @dtAddFrom IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRAddedTS >= @dtAddFrom
'
END
IF @dtAddTo IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRAddedTS <= @dtAddTo
'
END
IF @dtSCFrom IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRStatusChangeTS >= @dtSCFrom
'
END
IF @dtSCTo IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND pmr.PMRStatusChangeTS <= @dtSCTo
'
END
/********************** tbl_pmr_PmrMarkets******************************/
IF @Market IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND market.Market = @Market
'
END
/********************** tbl_pmr_audit *********************************/
IF @dtLCFrom IS NOT NULL
BEGIN
IF @AudLCAdded = 0
BEGIN
SET @SELECT = @SELECT + N'
JOIN (SELECT PMRNumber,TIMESTAMP=MAX(TimeStamp) FROM tbl_pmr_audit GROUP BY PMRNumber ) pmraudit ON
pmr.PMRNumber = pmraudit.PMRNumber
'
SET @AudLCAdded = 1
END
SET @WHERE = @WHERE + N'
AND pmraudit.TIMESTAMP >= @dtLCFrom
'
END
IF @dtLCTo IS NOT NULL
BEGIN
IF @AudLCAdded = 0
BEGIN
SET @SELECT = @SELECT + N'
JOIN (SELECT PMRNumber,TIMESTAMP=MAX(TimeStamp) FROM tbl_pmr_audit GROUP BY PMRNumber ) pmraudit ON
pmr.PMRNumber = pmraudit.PMRNumber
'
SET @AudLCAdded = 1
END
SET @WHERE = @WHERE + N'
AND pmraudit.TIMESTAMP <= @dtLCTo
'
END
/*************** tbl_pmr_ReleaseNotes **************************/
IF @iUSChecked IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.CheckUS,1) = @iUSChecked
'
END
IF @iCanadaChecked IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.iCanadaChecked,1) = @iCanadaChecked
'
END
IF @iFrenchChecked IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.CheckFrench,1) = @iFrenchChecked
'
END
IF @iUKChecked IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.CheckUK,1) = @iUKChecked
'
END
IF @iUIChangesChecked IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.Checked,1) = @iUIChangesChecked
'
END
IF @iTestNoteBlank IS NOT NULL
BEGIN
IF @iTestNoteBlank = 1
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.TestNote,'''') = ''''
'
END
ELSE IF @iTestNoteBlank = 2
BEGIN
SET @WHERE = @WHERE + N'
AND (rn.TestNote IS NOT NULL AND rn.TestNote != '''')
'
END
END
IF @iFrenchNoteBlank IS NOT NULL
BEGIN
IF @iFrenchNoteBlank = 1
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.FrenchNote,'''') = ''''
'
END
ELSE IF @iFrenchNoteBlank = 2
BEGIN
SET @WHERE = @WHERE + N'
AND (rn.FrenchNote IS NOT NULL AND rn.FrenchNote != '''')
'
END
END
IF @iExternalNoteBlank IS NOT NULL
BEGIN
IF @iExternalNoteBlank = 1
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.ExtNote,'''') = ''''
'
END
ELSE IF @iExternalNoteBlank = 2
BEGIN
SET @WHERE = @WHERE + N'
AND (rn.ExtNote IS NOT NULL AND rn.ExtNote != '''')
'
END
END
IF @iIntNote IS NOT NULL
BEGIN
IF @iIntNote = 1
BEGIN
SET @WHERE = @WHERE + N'
AND ISNULL(rn.IntNote,'''') = ''''
'
END
ELSE IF @iIntNote = 2
BEGIN
SET @WHERE = @WHERE + N'
AND (rn.IntNote IS NOT NULL AND rn.ExtNote != '''')
'
END
END
/******************** tbl_pmr_Status ******************************/
IF @sStatusType IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND s.StatusType = @sStatusType
'
END
/***************** tbl_PMR_Project ***********************/
IF @sPMGRelease IS NOT NULL
BEGIN
SET @WHERE = @WHERE + N'
AND proj.Release = @sPMGRelease
'
END
SET @CMD = @SELECT + @WHERE + N')
SELECT TOP 1 recordnum FROM Search_CTE
ORDER BY recordnum DESC
'
EXEC sp_executesql @CMD,N'
@nCurrentPage int,
@nPageSize int,
@nvchSortField nvarchar(max),
@nvchAscDesc nvarchar(10),
@iClient int,
@sSubmitter nvarchar(8),
@TypeID int,
@sAttr nvarchar(1),
@scorp nvarchar(1),
@iTotalClients int,
@iTotalSeats int,
@iTotalImpacted int,
@sClientRes nvarchar(10),
@sProject nvarchar(10),
@sSupervisor nvarchar(8),
@sProductManager nvarchar(8),
@sInReviewID nvarchar(8),
@sProgram nvarchar(20),
@QATestPMRs nvarchar(1),
@sSLAStatus nvarchar(1),
@dtAddFrom Datetime,
@dtAddTo Datetime,
@dtSCFrom Datetime,
@dtSCTo Datetime,
@dtLCFrom Datetime,
@dtLCTo Datetime,
@iUSChecked int,
@iCanadaChecked int,
@iFrenchChecked int,
@iUKChecked int,
@iUIChangesChecked int,
@iTestNoteBlank int,
@iFrenchNoteBlank int,
@iExternalNoteBlank int,
@iIntNote int,
@sStatusType nvarchar(1),
@sPMGRelease nvarchar,
@Market nvarchar(20),
@ProdTrio PlatformProductSubproduct READONLY,
@p_oStatBC PMRStatus READONLY,
@PMGstatBC PMGStatus READONLY,
@ProjectManagement ProjectManagament READONLY,
@Priority PMRPriorities READONLY,
@Keywords KeyWords READONLY',
@nCurrentPage=@nCurrentPage,
@nPageSize=@nPageSize,
@nvchSortField=@nvchSortField,
@nvchAscDesc=@nvchAscDesc,
@iClient=@iClient,
@sSubmitter=@sSubmitter,
@TypeID=@TypeID,
@sAttr=@sAttr,
@scorp=@sCorp,
@iTotalClients=@iTotalClients,
@iTotalSeats=@iTotalSeats,
@iTotalImpacted=@iTotalImpacted,
@sClientRes=@sClientRes,
@sProject=@sProject,
@sSupervisor=@sSupervisor,
@sProductManager=@sProductManager,
@sInReviewID=@sInReviewID,
@sProgram=@sProgram,
@QATestPMRs=@QATestPMRs,
@sSLAStatus=@sSLAStatus,
@dtAddFrom=@dtAddFrom,
@dtAddTo=@dtAddTo,
@dtSCFrom=@dtSCFrom,
@dtSCTo=@dtSCTo,
@dtLCFrom=@dtLCFrom,
@dtLCTo=@dtLCTo,
@iUSChecked=@iUSChecked,
@iCanadaChecked=@iCanadaChecked,
@iFrenchChecked=@iFrenchChecked,
@iUKChecked=@iUKChecked,
@iUIChangesChecked=@iUIChangesChecked,
@iTestNoteBlank=@iTestNoteBlank,
@iFrenchNoteBlank=@iFrenchNoteBlank,
@iExternalNoteBlank=@iExternalNoteBlank,
@iIntNote=@iIntNote,
@sStatusType=@sStatusType,
@sPMGRelease=@sPMGRelease,
@Market=@Market,
@ProdTrio=@ProdTrio,
@p_oStatBC=@p_oStatBC,
@PMGstatBC=@PMGstatBC,
@ProjectManagement=@ProjectManagement,
@Priority=@Priority,
@Keywords=@Keywords
SET @CMD = @SELECT + @WHERE + N')
SELECT * FROM Search_CTE'
IF @nCurrentPage > 0
BEGIN
SET @CMD = @CMD + N'
WHERE
pagenum = @nCurrentPage
ORDER BY recordnum'
END
PRINT @CMD
EXEC sp_executesql @CMD,N'
@nCurrentPage int,
@nPageSize int,
@nvchSortField nvarchar(max),
@nvchAscDesc nvarchar(10),
@iClient int,
@sSubmitter nvarchar(8),
@TypeID int,
@sAttr nvarchar(1),
@scorp nvarchar(1),
@iTotalClients int,
@iTotalSeats int,
@iTotalImpacted int,
@sClientRes nvarchar(10),
@sProject nvarchar(10),
@sSupervisor nvarchar(8),
@sProductManager nvarchar(8),
@sInReviewID nvarchar(8),
@sProgram nvarchar(20),
@QATestPMRs nvarchar(1),
@sSLAStatus nvarchar(1),
@dtAddFrom Datetime,
@dtAddTo Datetime,
@dtSCFrom Datetime,
@dtSCTo Datetime,
@dtLCFrom Datetime,
@dtLCTo Datetime,
@iUSChecked int,
@iCanadaChecked int,
@iFrenchChecked int,
@iUKChecked int,
@iUIChangesChecked int,
@iTestNoteBlank int,
@iFrenchNoteBlank int,
@iExternalNoteBlank int,
@iIntNote int,
@sStatusType nvarchar(1),
@sPMGRelease nvarchar,
@Market nvarchar(20),
@ProdTrio PlatformProductSubproduct READONLY,
@p_oStatBC PMRStatus READONLY,
@PMGstatBC PMGStatus READONLY,
@ProjectManagement ProjectManagament READONLY,
@Priority PMRPriorities READONLY,
@Keywords KeyWords READONLY',
@nCurrentPage=@nCurrentPage,
@nPageSize=@nPageSize,
@nvchSortField=@nvchSortField,
@nvchAscDesc=@nvchAscDesc,
@iClient=@iClient,
@sSubmitter=@sSubmitter,
@TypeID=@TypeID,
@sAttr=@sAttr,
@scorp=@sCorp,
@iTotalClients=@iTotalClients,
@iTotalSeats=@iTotalSeats,
@iTotalImpacted=@iTotalImpacted,
@sClientRes=@sClientRes,
@sProject=@sProject,
@sSupervisor=@sSupervisor,
@sProductManager=@sProductManager,
@sInReviewID=@sInReviewID,
@sProgram=@sProgram,
@QATestPMRs=@QATestPMRs,
@sSLAStatus=@sSLAStatus,
@dtAddFrom=@dtAddFrom,
@dtAddTo=@dtAddTo,
@dtSCFrom=@dtSCFrom,
@dtSCTo=@dtSCTo,
@dtLCFrom=@dtLCFrom,
@dtLCTo=@dtLCTo,
@iUSChecked=@iUSChecked,
@iCanadaChecked=@iCanadaChecked,
@iFrenchChecked=@iFrenchChecked,
@iIntNote=@iIntNote,
@iUKChecked=@iUKChecked,
@iUIChangesChecked=@iUIChangesChecked,
@iTestNoteBlank=@iTestNoteBlank,
@iFrenchNoteBlank=@iFrenchNoteBlank,
@iExternalNoteBlank=@iExternalNoteBlank,
@sStatusType=@sStatusType,
@sPMGRelease=@sPMGRelease,
@Market=@Market,
@ProdTrio=@ProdTrio,
@p_oStatBC=@p_oStatBC,
@PMGstatBC=@PMGstatBC,
@ProjectManagement=@ProjectManagement,
@Priority=@Priority,
@Keywords=@Keywords
November 16, 2010 at 9:24 am
Have you looked into Full Text Search?
I cant say I have had the pleasure of working with it... but if I recall correctly... it was designed for string searches...
November 16, 2010 at 9:27 am
OR! CLR RegEx search...
If you download the codes for the DB Sambles from CodePlex...
There is code there already that exposes the RegEx search into SQL...
That is... if your allowed to use CLR on your database 😀
November 16, 2010 at 10:32 am
Full Text Search only does wildcard searches where the beginning of the word is specified.
i.e.
If you do a wildcard search for RED you will get hits on
RED
REDmond
REDding
RED-orange
You won't get hits on
boRED
bRED
fRED
etc. It's sort of similar to using LIKE and requiring that the only % be put at the end of the keyword and not allowing it at the beginning.
Though this got some relief when we discovered two things.
The covering index I had created for the main table tbl_pmr_Pmrs was being ignored by the optimizier and it was going for a clustered index scan. Normally I religiously follow the optimizer, but the covering index would improve all the joins and it was INCLUDING both the description and the summary so there was no need to avoid it.
The other thing we discovered was that the keyword search was supposed to narrow down a search. So a search on two keywords Call and Center should only return records where the summary or the details had both the words call and center in them, not where one or the other was there.
So, changing the logic of the query a bit, and using *shudder* and index hint to make sure my covering index was used in all cases cut the query time in half for the long running keyword search, hitting in at the same speed it was running prior to our attempt to rewrite.
Non-keyword searches are consistently trumping the older method, but it was this one sort of search (from what I like to call the Nuclear Power Plant control panel search page) that was getting in our way.
However, to deal with the LIKE searches I did a bad thing. I basically wrote out the WHERE clause. i.e. I read all the reocrds and hard coded my dynamic SQL for each keyword. Because they had to be inclusive the other method I came up with (counting the number of search records and using a sub-query to see how many hits on the vairious keywords were there and tossing our records where the numbers didn't match) was losing the performance race.
i.e. I did this
SELECT @WHERE = @WHERE + N' AND (pmr.description LIKE '%' + keyword + '%' OR pmr.pmrsummary LIKE '%' + pmrsummary + '%')
FROM @keywords
Not proud of it, but I couldn't think of anything else that out performed it.
And we're presently avoiding CLR if possible.
November 26, 2010 at 3:51 pm
I've been meaning to write an article about just this sort of key word search, but I haven't quite got around to it yet. The type of search I'm going to describe works best on an 'AND' type condition. Meaning that all of the keywords must be present for the item or it's not a match.
I've done this successfully for 2 different types of clients - one of them a major wine distributor and the other a major auto parts distributor. In both cases I was using the descriptions in an Item table where the ITEM_NO was the primary key and therefore unique. One of the clients had double quotes in some of the item descriptions, which had to be stripped out before being useful since most of the items didn't have double quotes in their description columns.
One of the keys is using exactly the same logic to create a keywords table as when parsing the input keywords.
Here is a simple function to break out words that are delimited by a space. If it's likely that there are incorrect double spaces in either the user input to search for or in the table, then that would have to be handled in another step.
This code uses a Numbers table based on an article by Jeff Moden which you can find by searching for Tally or Numbers. You could also use the
newer Delimited8kSplit function that can also be found on this site.
CREATE FUNCTION [dbo].[TA_BreakOutWords]
(
@SVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT REPLACE(X.TheWord, '"', '') AS TheWord
FROM
(SELECT CAST(SUBSTRING(@S + ' ', N, CHARINDEX(' ', @S + ' ', N) - N) AS VARCHAR(50))
AS TheWord
FROM dbo.Nbrs WITH (NOLOCK)
WHERE N > 0 AND N < LEN(@S) + LEN(' ')
AND SubString(' ' + @S + ' ', N, 1) = ' '
) AS X
WHERE LEN(X.TheWord) > 1
AND LTRIM(RTRIM(X.TheWord)) <> ''
)
Here is the keyword table:
CREATE TABLE [dbo].[TA_KeyWordItems](
[ItemNo] [dbo].[T_ITEM_NO] NOT NULL,
[KeyWord] [varchar](30) NOT NULL,
CONSTRAINT [PK_TA_KeyWordItems] PRIMARY KEY CLUSTERED
(
[ItemNo] ASC,
[KeyWord] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here is the procedure I used to initially populate the keyword table. It gets all inventory type items from the item table, breaks out the words in the descriptions, weeds out the duplicates (which means that when an item has the same word twice in its description, only 1 of them is used), then inserts them into the keywords table:
CREATE PROCEDURE [dbo].[TA_KeyWordsPopulate]
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE TA_KeyWordItems
SELECT
RecID = IDENTITY(INT, 1, 1)
, CAST(I.ITEM_NO AS VARCHAR(15)) AS ItemNo, CAST(BOW.TheWord AS VARCHAR(20)) AS TheWord
INTO #IW
FROM IM_ITEM I
CROSS APPLY (SELECT TheWord FROM dbo.TA_BreakOutWords(I.DESCR)) AS BOW
WHERE
I.ITEM_TYP = 'I'
CREATE CLUSTERED INDEX IW_Tmp ON #IW (ItemNo, TheWord)
-- I always have a table for keywords to exclude. This way I can weed out
-- silly things like the ampersand in wine descriptions.
DELETE IW
FROM #IW IW
INNER JOIN TA_KeyWordsToExclude KWE ON
IW.TheWord = KWE.KeyWord
-- This deletes the duplicates.
DELETE IW
FROM #IW IW
INNER JOIN
(SELECT MIN(RecID) AS MinRecID, ItemNo, TheWord
FROM #IW
GROUP BY ItemNo, TheWord
HAVING COUNT(*) > 1
) AS X ON
IW.ItemNo = X.ItemNo
AND IW.TheWord = X.TheWord
AND IW.RecID <> X.MinRecID
INSERT INTO TA_KeyWordItems
( ItemNo, KeyWord )
SELECT
IW.ItemNo, IW.TheWord
FROM #IW IW
END
There are various ways to keep the keywords table up to date. A trigger on the item table that will delete and re-insert the keywords when a description is updated is one way.
Here is how the search works:
1. Get the user input and create a temp table using the same function
and duplicate rules that was used in creating the keywords table.
2. Count the keywords in the temp table.
3. There's a match when the temp table joins to the keywords table
the same number of times as the count of keywords.
Here's a snippet of code that does a keywords search:
DECLARE
@KWCountINT
CREATE TABLE #KW
( RecIDINT IDENTITY(1, 1)
, KeyWordVARCHAR(50))
INSERT INTO #KW
( KeyWord )
SELECT TheWord FROM dbo.TA_BreakOutWords(@ItemSearchQuery)
-- Again get rid of garbage words or words not to be used in searching.
DELETE KW
FROM #KW KW
INNER JOIN KeyWordsToExclude KWE ON
KW.KeyWord = KWE.KeyWord
-- Make sure no duplicates in the user input.
DELETE KW
FROM #KW KW
INNER JOIN
(SELECT MIN(RecID) AS MinRecID, KeyWord
FROM #KW
GROUP BY KeyWord
HAVING COUNT(*) > 1
) AS X ON
KW.KeyWord = X.KeyWord
AND KW.RecID <> X.MinRecID
-- Get a count of words used in the search.
SELECT @KWCount = X.RC
FROM
(SELECT COUNT(*) AS RC FROM #KW
) AS X
-- Create a stub table of items that match the keywords from user input.
SELECT
I.ItemNo
INTO #ItemStub
FROM #KW KW
INNER JOIN TA_KeyWordItems I ON
KW.KeyWord = I.KeyWord
GROUP BY I.ItemNo
HAVING COUNT(*) = @KWCount
You should now have a stub table of all items that match the user input.
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply