asked on

How is the value found for "Update Rule" or "Update Rule" on a foreign key

In SQL 2005 there are settings on Foreign Keys for Delete Rule and Update Rule.

Each of these has the following options:

No Action
Cascade
Set Null
Set Default

You can select the value that is set using sp_fkeys but only a 1 or 0 is returned with the following results for the Delete or Update rule.

Not Set: 1
Cascade: 0
Set Null: 1
Set Default: 1

Is there any way to determine through a query which of these values is set?

Also if Set Null or Set Default is set is a delete allowed but the child records are left orphaned?

I'm working on a middle tier and want to include these values in the class that represents the table.

DECLARE @RC int
Declare @pktable_name varchar(50)
 
set @pktable_name = 'tblTableName'
EXECUTE @RC = [DatabaseName].[dbo].[sp_fkeys] 
      @pktable_name

Open in new window

Daniel Wilson

I got this through taking a look @ the code in sp_fkeys.

Is it what you need?


Select O.Name, F.name as KeyName,             UPDATE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsUpdateCascade')
                                            when 1 then 0
                                            else        1
                                            end),
            DELETE_RULE         = convert(smallint,
                                            case ObjectProperty(f.object_id, 'CnstIsDeleteCascade')
                                            when 1 then 0
                                            else        1
                                            end)
from sys.objects O  inner join sys.foreign_keys f on O.object_id = f.referenced_object_id
Where O.Name = 'tblMain_Part'

Open in new window

Daniel Wilson

The O.Name should be compared to your @TableName variable ... I posted w/ my hard-coded test value.

steverey443

ASKER

Thanks but this is still only returning a 1 or 0 for UPDATE_RULE and DELETE_RULE when there are four options. Two options are listed below.

It looks like Cascade returns 0 and everything else (Not Set, Set Null, Set Default) return 1.

I'm looking for a way to distinguish between each of the four settings.

tblParent1      FK_tblChild1_tblParent1            1      0
tblParent1      FK_tblChild1_tblParent11            1      1
tblParent1      FK_tblChild2_tblParent1            1      1

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild2_tblParent1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild2]'))
ALTER TABLE [dbo].[tblChild2] WITH CHECK ADD CONSTRAINT [FK_tblChild2_tblParent1] FOREIGN KEY([Parent1Key])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
ON UPDATE SET DEFAULT
ON DELETE SET DEFAULT

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild1_tblParent1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild1]'))
ALTER TABLE [dbo].[tblChild1] WITH CHECK ADD CONSTRAINT [FK_tblChild1_tblParent1] FOREIGN KEY([Parent1Key_1])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
ON DELETE CASCADE
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild2_tblParent1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild2]'))
ALTER TABLE [dbo].[tblChild2] WITH CHECK ADD CONSTRAINT [FK_tblChild2_tblParent1] FOREIGN KEY([Parent1Key])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
ON UPDATE SET DEFAULT
ON DELETE SET DEFAULT

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_executor')
CREATE USER [db_executor] FOR LOGIN [db_executor] WITH DEFAULT_SCHEMA=[dbo]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'jstsend')
EXEC sys.sp_executesql N'CREATE SCHEMA [jstsend] AUTHORIZATION [jstsend]'
 
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'JSTMoviesUser')
EXEC sys.sp_executesql N'CREATE SCHEMA [JSTMoviesUser] AUTHORIZATION [JSTMoviesUser]'
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvalScore]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tvalScore](
	[ScoreKey] [int] NOT NULL,
	[ScoreValue] [decimal](3, 2) NULL,
	[ScoreSeq] [smallint] NULL,
	[ScoreCreateTS] [smalldatetime] NULL CONSTRAINT [DF_tvalScore_ScoreCreateTS]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tvalScore] PRIMARY KEY CLUSTERED 
(
	[ScoreKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysConfig_System]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysConfig_System](
	[Config_SystemKey] [int] NOT NULL,
	[SysConfigDatabaseName] [varchar](255) NULL,
	[SysConfigMoviesIntroductionText] [varchar](2000) NULL,
	[TS] [binary](8) NULL,
 CONSTRAINT [PK_tsysConfig_System] PRIMARY KEY CLUSTERED 
(
	[Config_SystemKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsysVersion]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsysVersion](
	[VersionKey] [int] NOT NULL,
	[Version] [nvarchar](50) NULL,
	[VersionStatus] [nvarchar](50) NULL,
	[VersionWarning] [nvarchar](255) NULL,
	[VersionCreateTS] [smalldatetime] NULL,
	[TS] [binary](8) NULL,
	[VersionApplicationName] [nvarchar](50) NULL,
	[VersionFolder] [nvarchar](255) NULL,
	[VersionNotes] [nvarchar](255) NULL,
	[VersionLastLaunched] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_SelectPersonsAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[jsp_SelectPersonsAll]
AS
/*
    Author :	
    Date :	
    Notes :	
    Change Log :3/20/2007 - JC - added active criteria and all selection
	 
*/
SELECT 
	0 as PersonKey,
	''--ALL--'' as PersonName
FROM
	tblPerson
 
Union
 
SELECT
	[PersonKey],
	[PersonName]
	
FROM
	[dbo].[tblPerson]
 
ORDER BY
	PersonKey, PersonName 
 
 
 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_SelectMovieComments]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
 
CREATE PROCEDURE [dbo].[jsp_SelectMovieComments]
(@MovieKey int)
AS
 
 
SELECT
	[CommentKey],
	[MovieKey],
	[CommentText],
	[PersonKey]
FROM
	[dbo].[tblMovieComment]
where MovieKey= @MovieKey
 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_MoviesPaging]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_MoviesPaging]
@PersonKey int,
@CurrentPage int,
@PageSize int
 
 
AS
 --Create a temp table to hold the current page of data
 --Add and ID column to count the records
 CREATE TABLE #TempTable
 (
  ID int IDENTITY PRIMARY KEY,
  MovieKey int,
  MovieDisplayTitle varchar(100),
  MovieTitle varchar(100),
  AvgScore decimal(3,2),
  Votes int
  
 )
 --Fill the temp table with the Customers data
DECLARE @strSQL varchar(1200)
DECLARE @WhereClause varchar(1000)
 
 
	Set @WhereClause = ''''
 
                 SET @strSQL = 		''INSERT INTO #TempTable ''
	SET @strSQL = @strSQL + 	''( MovieKey,  MovieDisplayTitle,  MovieTitle,  AvgScore,  Votes ) ''
	SET @strSQL = @strSQL +	'' Select MovieKey,  MovieDisplayTitle,  MovieTitle,  AvgScore, Votes ''
	SET @strSQL = @strSQL +	'' from jvw_AvgMovieScores ''
	
  	 If @PersonKey > 0 
             		Set @WhereClause=''where MovieKey in (Select MovieKey from txrfMoviePerson where PersonKey = '' +STR( @PersonKey) + '') ''
	
 
	Set @strSQL = @strSQL + @WhereClause
	Print(@strSQL)
 
 --Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
Declare @TotalRecords int
 
 
DECLARE @stringSQL varchar(1200)
 
 
SET @stringSQL= ''SELECT * FROM  #TempTable  WHERE  ID >'' 
SET @stringSQL = @stringSQL + STR(@FirstRec)  + '' AND  ID < '' + STR( @LastRec) 
 
SET @stringSQL = @stringSQL + ''  SELECT '' +  STR(@TotalRecords) + '' = COUNT(*) FROM #TempTable ''
 
 
Print(@stringSQL)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDecimalNumberTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDecimalNumberTest](
	[DecimalNumberTestKey] [int] IDENTITY(1,1) NOT NULL,
	[NumericTest1_0] [numeric](1, 0) NULL,
	[DecimalTest1_0] [decimal](1, 0) NULL,
	[NumericTest9_2] [numeric](9, 2) NULL,
	[DecimalTest5_5] [decimal](5, 5) NULL,
	[Varbinary4000] [varbinary](4000) NULL,
	[NVarchar1000] [nvarchar](1000) NULL,
	[Varchar1000] [varchar](1000) NULL,
	[Char8000] [char](8000) NULL,
 CONSTRAINT [PK_tblDecimalNumberTest] PRIMARY KEY CLUSTERED 
(
	[DecimalNumberTestKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblNCharTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblNCharTest](
	[NCharTestKey] [int] NOT NULL,
	[nChar4000] [nchar](4000) NULL,
 CONSTRAINT [PK_tblNCharTest] PRIMARY KEY CLUSTERED 
(
	[NCharTestKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMovieComment]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblMovieComment](
	[MovieCommentKey] [int] IDENTITY(1,1) NOT NULL,
	[MovieKey] [int] NOT NULL,
	[CommentText] [varchar](1500) NULL,
	[PersonKey] [int] NOT NULL,
 CONSTRAINT [PK_tblMovieComment] PRIMARY KEY CLUSTERED 
(
	[MovieCommentKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jxp_Contact_Insert] AS SELECT 1' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_GetDateOnly]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
 
 CREATE FUNCTION [dbo].[jfn_GetDateOnly]
 
 ( @pInputDate    DATETIME )
 
RETURNS DATETIME
/* 
Copyright Notice: The programming code contained within this stored procedure is owned exclusively by J Street Technology.  
This stored procedure is licensed to the purchaser of this stored procedure according to the terms described in the CartGenie Software 
Licensing Agreement.  Under the terms of the CartGenie Software Licensing Agreement, any attempt to copy, modify, or redistribute 
this stored procedure or any of its programming code is a violation of the CartGenie Software Licensing Agreement.  J Street Technology 
will vigorously prosecute any violators of the CartGenie Software Licensing Agreement under the rules set forth by U.S. 
and International copyright laws.  For questions regarding the CartGenie Software Licensing Agreement, please contact
J Street Technology.
 
Function Purpose: strip of time portion from date input
Author: J Street Technology (Eric Isaacs)
Last Update: 03/06/2007
*/	
BEGIN
 
    RETURN CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) AS DATETIME)
 
END
' 
END
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jxp_Contact_Update] AS SELECT 1' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_SelectPersonActive]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[jsp_SelectPersonActive]
AS
/*
    Author :	Jessica Chong
    Date :	3/20/2007
    Notes :	this limits the list to active people
    Change Log :3/20/2007 - JC - created sproc
	 
*/
SELECT 
	0 as PersonKey,
	''--Select a Person--'' as PersonName
FROM
	tblPerson
 
Union
 
SELECT
	[PersonKey],
	[PersonName]
	
FROM
	[dbo].[tblPerson]
WHERE 
	PersonActive <> 0  
ORDER BY
	PersonKey, PersonName 
 
 
 
 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblParent1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblParent1](
	[Parent1Key] [int] IDENTITY(1,1) NOT NULL,
	[ParentName] [varchar](4000) NULL,
 CONSTRAINT [PK_tblParent1] PRIMARY KEY CLUSTERED 
(
	[Parent1Key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getPersonByKey]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_getPersonByKey]
(
 
@PersonKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
 
select  * 
from  tblPerson 
where PersonKey = @PersonKey
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getCommentsScoresByPerson_Movie]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_getCommentsScoresByPerson_Movie]
(
@PersonKey int = -1,
@MovieKey int = -1 
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
DECLARE @Where varchar(250)
DECLARE @sql varchar(250)
 
 
SET @sql = '' select  * from  txrfMoviePerson ''
BEGIN
IF @MovieKey <> -1 
SET @Where = '' where MovieKey = '' + LTrim(RTrim(STR(@MovieKey)))
ELSE
SET @Where = '' where 1=1 ''
IF @PersonKey <> -1
SET @Where = @Where + '' and PersonKey = '' + LTrim(RTrim(STR(@PersonKey))) 
END 
 
SET @sql = @sql + @Where
 
--Print (@sql)
EXEC(@sql)
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movies_New]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE   Procedure [dbo].[jsp_Movies_New]
(
	@LetterFilter varchar(5) = ''ALL'',	
	@startScore decimal(3,2) = NULL,
	@endScore decimal(3,2) = NULL
)
 
AS
/*
    Author :Jessica
    Date :	3/22/2007
    Notes :	Return information for Movies for display, limited by @WhereClause if passed
    Change Log : 3/22/2007- JC - Sproc created
	
 
--*/
 
 
DECLARE @strSQL varchar(1200)
DECLARE @WhereClause varchar(1000)
 
 
SET @WhereClause = ''''
 
IF @LetterFilter <> ''ALL''
	BEGIN
		Set @WhereClause = '' WHERE  MovieDisplayTitle LIKE '''''' + LTrim(RTrim(@LetterFilter)) + ''%'''' ''
	END
 
ELSE
	BEGIN
		SET @WhereClause = '' WHERE 1=1 ''
	END 
 
IF NOT @startScore IS NULL OR  NOT @endScore IS NULL 
BEGIN
 
  SELECT 
 	@WhereClause = 
	CASE 
		WHEN ( @startScore IS NOT NULL and  @endScore  IS NOT NULL ) THEN
			@WhereClause + '' AND AvgScore BETWEEN '''''' +Cast(@StartScore as varchar(12)) +  '''''' AND '''''' + Cast(@endScore as varchar(12)) + '''''' '' 
		
		WHEN (@startScore IS NOT NULL) THEN
			@WhereClause + '' AND AvgScore >= '''''' + Cast(@StartScore as varchar(12)) + '''''' ''
		
		WHEN ( @endScore IS NOT NULL) THEN
			@WhereClause +  '' AND AvgScore <= '''''' + Cast(@endScore as varchar(12)) + '''''' ''
	END
  
END
 
BEGIN
	SET @strSQL = ''SELECT MovieKey, MovieDisplayTitle as Title, MovieTitle as SortTitle, AvgScore, Votes, IMDBID FROM jvw_AvgMovieScores '' + @WhereClause + '' ORDER BY MovieDisplayTitle''
END
 
Exec(@strSQL)
 
--PRINT(@strSQL)
 
 
 
 
 
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Person_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create Procedure [dbo].[jsp_Person_Delete]
	(@PersonKey int)
AS
/*
    Author :	Jessica
    Date :	3/22/2007
    Notes :	Delete a Person record
    Change Log : 
*/
Delete from tblPerson
WHERE     (PersonKey = @PersonKey)
return @@ROWCOUNT
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Person_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create  Procedure [dbo].[jsp_Person_Insert]
(
	@Name varchar(255),
    @UserID varchar(50) = NULL,
     @Password varchar(50)=NULL
)
AS
/*
    Author :	Jessica Chong
    Date :	3/22/2007
    Notes :	Insert a Movie record
    Change Log : 
*/
 
DECLARE @PersonKey int
SEt @PersonKey = -1 
	insert tblPerson
	(
	PersonName,
	PersonUID,
	PersonPWD,
	PersonActive
	)
	values
	(
	@Name,
    @UserID,
    @Password, 
	1
	)
	select @PersonKey = @@IDENTITY
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_ValidateUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_ValidateUser]
(
@UserId varchar(50),
@pwd varchar(50)
 
)
AS
/*
    Author :Jessica Chong
    Date :	03/22/2007
    Notes :	Return movies filtered by person
    Change Log : 
*/
 
 
Select Personkey 
From tblPerson
where PersonUID = @UserId and 
		Cast(PersonPWD as varbinary(50)) = cast(@pwd as varbinary(50))
		
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getReviews]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_getReviews]
 
AS
/*
    Author :Jessica Chong
    Date :	03/22/2007
    Notes :	Return movies filtered by person
    Change Log : 
*/
 
 
Select      * 
from       jvw_AvgReviewers
Order By  Reviewer, LastMovieDate desc
 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPerson]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblPerson](
	[PersonKey] [int] IDENTITY(1,1) NOT NULL,
	[PersonName] [varchar](255) NULL,
	[PersonUID] [varchar](50) NULL,
	[PersonPWD] [varchar](50) NULL,
	[PersonCreateTS] [smalldatetime] NULL,
	[PersonActive] [int] NULL CONSTRAINT [DF_tblPerson_PersonActive_1]  DEFAULT (1),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tblPerson_1] PRIMARY KEY CLUSTERED 
(
	[PersonKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_GetPersonLastMovieDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE  FUNCTION [dbo].[jfn_GetPersonLastMovieDate] (@PersonKey int)  
RETURNS smalldatetime  AS  
BEGIN 
/*
    Author : Steve Senter
    Date :7/11/2003
    Known Issues : none 
    Notes : Function to return the date of the last movie reviewed for the passed PersonKey
    Input Parameters : PersonKey
    Change Log :  7/11/2003 -- SJS -- Function created
*/
 
DECLARE @Date as smalldatetime
 
	SELECT     @Date = MAX(MoviePersonCreateTS)
	FROM         dbo.txrfMoviePerson
	GROUP BY PersonKey
	HAVING      (PersonKey =@PersonKey)
 
	return @Date
END
 
 
 
' 
END
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VIEW1]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VIEW1]
AS
SELECT     dbo.tblMovie.MovieKey, dbo.tblMovie.MovieTitle, dbo.tvalRating.RatingValue, dbo.tblPerson.PersonKey, ISNULL(dbo.tblPerson.PersonName, 
                      ''Movie not yet reviewed'') AS PersonName, ISNULL(dbo.tvalScore.ScoreValue, 0) AS ScoreValue, dbo.txrfMoviePerson.MoviePersonComment, 
                      dbo.txrfMoviePerson.MoviePersonCreateTS AS EntryDate, ISNULL(dbo.tblMovie.MovieIMDBID, - 1) AS IMDBID
FROM         dbo.tvalRating RIGHT OUTER JOIN
                      dbo.tblMovie ON dbo.tvalRating.RatingKey = dbo.tblMovie.RatingKey LEFT OUTER JOIN
                      dbo.tvalScore RIGHT OUTER JOIN
                      dbo.txrfMoviePerson ON dbo.tvalScore.ScoreKey = dbo.txrfMoviePerson.ScoreKey LEFT OUTER JOIN
                      dbo.tblPerson ON dbo.txrfMoviePerson.PersonKey = dbo.tblPerson.PersonKey ON dbo.tblMovie.MovieKey = dbo.txrfMoviePerson.MovieKey
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblTest](
	[TestKey] [int] IDENTITY(1,1) NOT NULL,
	[GUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblContactPWDChangeRequest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblContactPWDChangeRequest](
	[ContactPWDChangeRequestKey] [int] IDENTITY(1,1) NOT NULL,
	[ContactKey] [int] NOT NULL,
	[GUID] [uniqueidentifier] NOT NULL,
	[RequestDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_tblContactPWDChangeRequest] PRIMARY KEY CLUSTERED 
(
	[ContactPWDChangeRequestKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblContact]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblContact](
	[ContactKey] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblContact] PRIMARY KEY CLUSTERED 
(
	[ContactKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zzjsp_Person_AddUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[zzjsp_Person_AddUpdate] 
	@PersonKey int OUTPUT,
	@PersonName varchar(255),
	@PersonUID varchar(255),
	@PersonPWD varchar(255)
/*
    Author :	Steve Senter
    Date :	8/6/2003
    Issues :	NOT CURRENTLY USED
    Notes :	Insert or Update a Person record
    Change Log : 8/6/2003 - SJS - Sproc created
*/
AS
 
If (@PersonKey = -1)	--New Record
    Begin
	Insert into tblPerson (PersonName, PersonUID, PersonPWD)
	Values (@PersonName, @PersonUID, @PersonPWD)
	set @PersonKey = @@Identity
    End
 
Else
    Begin
	Update tblPerson
	Set PersonName = @PersonName,
		PersonUID = @PersonUID,
		PersonPWD = @PersonPWD
	Where PersonKey = @PersonKey
    End' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvalRating]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tvalRating](
	[RatingKey] [int] NOT NULL,
	[RatingValue] [varchar](50) NULL,
	[RatingSeq] [int] NULL,
	[RatingCreateTS] [smalldatetime] NULL CONSTRAINT [DF_tvalRating_RatingCreateTS]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tvalRating] PRIMARY KEY CLUSTERED 
(
	[RatingKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPersonTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblPersonTest](
	[PersonTestKey] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](255) NULL,
	[LastName] [varchar](255) NULL,
	[CarMake] [varchar](255) NULL,
	[CarModel] [varchar](255) NULL,
 CONSTRAINT [PK_tblPersonTest] PRIMARY KEY CLUSTERED 
(
	[PersonTestKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[txrfMoviePerson]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[txrfMoviePerson](
	[MoviePersonKey] [int] IDENTITY(1,1) NOT NULL,
	[MovieKey] [int] NOT NULL,
	[PersonKey] [int] NOT NULL,
	[ScoreKey] [int] NULL,
	[MoviePersonComment] [varchar](255) NULL,
	[MoviePersonCreateTS] [smalldatetime] NULL CONSTRAINT [DF_txrfMoviePerson_MoviePersonCreateTS]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_txrfMoviePerson] PRIMARY KEY CLUSTERED 
(
	[MoviePersonKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Movie_Person] UNIQUE NONCLUSTERED 
(
	[MovieKey] ASC,
	[PersonKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblChild2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblChild2](
	[Child2Key] [int] IDENTITY(1,1) NOT NULL,
	[Child2Name] [varchar](50) NULL,
	[Parent1Key] [int] NULL,
 CONSTRAINT [PK_tblChild2] PRIMARY KEY CLUSTERED 
(
	[Child2Key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblChild1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblChild1](
	[Child1Key] [int] IDENTITY(1,1) NOT NULL,
	[Child1Name] [varchar](4000) NULL,
	[Parent1Key_1] [int] NULL,
	[Parent1Key_2] [int] NULL,
 CONSTRAINT [PK_tblChild1] PRIMARY KEY CLUSTERED 
(
	[Child1Key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTest2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblTest2](
	[Test2Key] [int] IDENTITY(1,1) NOT NULL,
	[MovieKey] [int] NOT NULL,
	[Test1] [varchar](255) NULL,
 CONSTRAINT [PK_tblTest2] PRIMARY KEY CLUSTERED 
(
	[Test2Key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMovie]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblMovie](
	[MovieKey] [int] IDENTITY(1,1) NOT NULL,
	[MovieDisplayTitle] [varchar](255) NULL,
	[MovieSortTitle] [varchar](255) NULL,
	[RatingKey] [int] NULL,
	[MovieIMDBID] [varchar](50) NULL,
	[MovieCreateTS] [smalldatetime] NULL CONSTRAINT [DF_tblMovie_MovieCreateTS]  DEFAULT (getdate()),
	[TS] [timestamp] NULL,
 CONSTRAINT [PK_tblMovie] PRIMARY KEY CLUSTERED 
(
	[MovieKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_ReviewerSummary]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author :	Steve Senter
    Date :	7/11/2003
    Notes :	Summary scores for Movies
		MUST KEEP THE ORDER OF OUTPUT FIELDS, FOR USE ON WEB PAGES (INDEXES)
    Change Log : 7/18/2003 SJS Changed MovieTitle to MovieDisplayTitle
		7/11/2003 - SJS - View created
 
*/CREATE VIEW [dbo].[jvw_ReviewerSummary]
AS
SELECT     dbo.tblPerson.PersonKey, dbo.tblPerson.PersonName AS Reviewer, dbo.txrfMoviePerson.MoviePersonCreateTS AS EntryDate, 
                      dbo.tblMovie.MovieKey, dbo.tblMovie.MovieDisplayTitle, ISNULL(dbo.tvalScore.ScoreValue, 0) AS ScoreValue, 
                      dbo.txrfMoviePerson.MoviePersonComment AS Comment
FROM         dbo.tblPerson INNER JOIN
                      dbo.txrfMoviePerson ON dbo.tblPerson.PersonKey = dbo.txrfMoviePerson.PersonKey INNER JOIN
                      dbo.tblMovie ON dbo.txrfMoviePerson.MovieKey = dbo.tblMovie.MovieKey LEFT OUTER JOIN
                      dbo.tvalScore ON dbo.txrfMoviePerson.ScoreKey = dbo.tvalScore.ScoreKey
 
 
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Score_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Score_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ScoreKey,
	ScoreValue,
	ScoreSeq,
	ScoreCreateTS	
	
FROM
	[dbo].[tvalScore]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_AvgMovieScores]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author :	Steve Senter
    Date :	7/3/2003
    Issues :	none 
    Notes :	Average scores for Movies
    Change Log : 2/11/2005 SJS Used Convert() to format AvgScore output
		7/18/2003 SJS Changed MovieTitle to MovieDisplayTitle and added MovieSortTitle
		7/3/2003 - SJS - View created
*/
CREATE VIEW [dbo].[jvw_AvgMovieScores]
AS
SELECT     TOP 100 PERCENT dbo.tblMovie.MovieKey, dbo.tblMovie.MovieDisplayTitle, ISNULL(dbo.tblMovie.MovieSortTitle, dbo.tblMovie.MovieDisplayTitle) 
                      AS MovieTitle, CONVERT(decimal(3, 2), AVG(ISNULL(dbo.tvalScore.ScoreValue, 0))) AS AvgScore, 
                      CASE WHEN SUM(ISNULL(dbo.txrfMoviePerson.MoviePersonKey, 0)) = 0 THEN 0 ELSE COUNT(dbo.txrfMoviePerson.MoviePersonKey) END AS Votes, 
                      ISNULL(dbo.tblMovie.MovieIMDBID, '''') AS IMDBID, CASE WHEN isnull(dbo.tblMovie.MovieIMDBID, '''') = '''' THEN '''' ELSE ''GO'' END AS IMDB_Text, 
                      dbo.tblMovie.MovieCreateTS AS MovieCreatedTime
FROM         dbo.txrfMoviePerson INNER JOIN
                      dbo.tvalScore ON dbo.txrfMoviePerson.ScoreKey = dbo.tvalScore.ScoreKey RIGHT OUTER JOIN
                      dbo.tblMovie ON dbo.txrfMoviePerson.MovieKey = dbo.tblMovie.MovieKey
GROUP BY dbo.tblMovie.MovieDisplayTitle, dbo.tblMovie.MovieKey, ISNULL(dbo.tblMovie.MovieIMDBID, ''''), ISNULL(dbo.tblMovie.MovieSortTitle, 
                      dbo.tblMovie.MovieDisplayTitle), dbo.tblMovie.MovieCreateTS, ISNULL(dbo.tblMovie.MovieSortTitle, dbo.tblMovie.MovieDisplayTitle)
ORDER BY ISNULL(dbo.tblMovie.MovieSortTitle, dbo.tblMovie.MovieDisplayTitle), dbo.tblMovie.MovieCreateTS DESC
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[23] 4[24] 2[31] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -96
         Left = 0
      End
      Begin Tables = 
         Begin Table = "txrfMoviePerson"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 241
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tvalScore"
            Begin Extent = 
               Top = 6
               Left = 279
               Bottom = 121
               Right = 448
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tblMovie"
            Begin Extent = 
               Top = 126
               Left = 38
               Bottom = 241
               Right = 217
            End
            DisplayFlags = 280
            TopColumn = 3
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 12
         Column = 10755
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'jvw_AvgMovieScores'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'jvw_AvgMovieScores'
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_SelectScoresAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[jsp_SelectScoresAll]
AS
 
 
SELECT
	[ScoreKey],
	[ScoreValue],
	[ScoreSeq],
	[ScoreCreateTS],
	[TS]
FROM
	[dbo].[tvalScore]
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Score_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Score_Get]
	@ScoreKey int
AS
BEGIN --Procedure	
 
SELECT
	ScoreKey,
	ScoreValue,
	ScoreSeq,
	ScoreCreateTS	
	
FROM
	[dbo].[tvalScore]
WHERE
	[ScoreKey] = @ScoreKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_MovieScoreSummary]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author :	Steve Senter
    Date :	7/8/2003
    Notes :	Summary scores for Movies
		MUST KEEP THE ORDER OF OUTPUT FIELDS, FOR USE ON WEB PAGES (INDEXES)
    Change Log : 7/23/2003 SJS Added MoviePersonKey
		7/18/2003 SJS Changed MovieTitle to MovieDisplayTitle and added MovieSortTitle
		7/8/2003 - SJS - View created
 
*/
CREATE VIEW [dbo].[jvw_MovieScoreSummary]
AS
SELECT     dbo.tblMovie.MovieKey, dbo.tblMovie.MovieDisplayTitle, dbo.tblPerson.PersonKey, ISNULL(dbo.tblPerson.PersonName, ''Movie not yet reviewed'') 
                      AS PersonName, ISNULL(dbo.tvalScore.ScoreValue, 0) AS ScoreValue, dbo.txrfMoviePerson.MoviePersonComment, 
                      dbo.txrfMoviePerson.MoviePersonCreateTS AS EntryDate, ISNULL(dbo.tblMovie.MovieIMDBID, - 1) AS IMDBID, ISNULL(dbo.tblMovie.MovieSortTitle, 
                      dbo.tblMovie.MovieDisplayTitle) AS MovieSortTitle, dbo.txrfMoviePerson.MoviePersonKey
FROM         dbo.tblPerson RIGHT OUTER JOIN
                      dbo.tblMovie INNER JOIN
                      dbo.txrfMoviePerson ON dbo.tblMovie.MovieKey = dbo.txrfMoviePerson.MovieKey LEFT OUTER JOIN
                      dbo.tvalScore ON dbo.txrfMoviePerson.ScoreKey = dbo.tvalScore.ScoreKey ON dbo.tblPerson.PersonKey = dbo.txrfMoviePerson.PersonKey
 
 
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Score_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Score_Delete]
	@ScoreKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tvalScore]
WHERE
	[ScoreKey] = @ScoreKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Score_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Score_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ScoreKey,
	ScoreValue,
	ScoreSeq,
	ScoreCreateTS	
	
FROM
	[dbo].[tvalScore]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Score_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Score_Insert]
	@ScoreValue decimal,
	@ScoreSeq smallint,	
	@ScoreKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tvalScore] (
	[ScoreValue],
	[ScoreSeq]		
) VALUES (
	@ScoreValue,
	@ScoreSeq	
)
 
SET @ScoreKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getScores]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_getScores]
 
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
select     -1 as Scorekey,
			 ''- Select a Score -'' as ScoreValue,
			 0 as ScoreSeq 
 
from 
		tvalScore 
UNION 
 
SELECT 
		Scorekey,
		 cast(ScoreValue AS varchar(50)),
		 ScoreSeq 
FROM 
		tvalScore 
	ORDER BY ScoreSeq' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Score_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Score_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Score_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Score_Update]
	@ScoreKey int,
	@ScoreValue decimal,
	@ScoreSeq smallint	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tvalScore] SET
	
	[ScoreValue]  = @ScoreValue,
	[ScoreSeq]  = @ScoreSeq	
 
WHERE
	[ScoreKey] = @ScoreKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Config_System_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Config_System_Delete]
	@Config_SystemKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tsysConfig_System]
WHERE
	[Config_SystemKey] = @Config_SystemKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Config_System_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Config_System_Get]
	@Config_SystemKey int
AS
BEGIN --Procedure	
 
SELECT
	Config_SystemKey,
	SysConfigDatabaseName,
	SysConfigMoviesIntroductionText	
	
FROM
	[dbo].[tsysConfig_System]
WHERE
	[Config_SystemKey] = @Config_SystemKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Config_System_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Config_System_Update]
	@Config_SystemKey int,
	@SysConfigDatabaseName varchar (255),
	@SysConfigMoviesIntroductionText varchar (2000)	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tsysConfig_System] SET
	
	[SysConfigDatabaseName]  = @SysConfigDatabaseName,
	[SysConfigMoviesIntroductionText]  = @SysConfigMoviesIntroductionText	
 
WHERE
	[Config_SystemKey] = @Config_SystemKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Config_System_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Config_System_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	Config_SystemKey,
	SysConfigDatabaseName,
	SysConfigMoviesIntroductionText	
	
FROM
	[dbo].[tsysConfig_System]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Config_System_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Config_System_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	Config_SystemKey,
	SysConfigDatabaseName,
	SysConfigMoviesIntroductionText	
	
FROM
	[dbo].[tsysConfig_System]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Config_System_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Config_System_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Config_System_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Config_System_Insert]
	@SysConfigDatabaseName varchar (255),
	@SysConfigMoviesIntroductionText varchar (2000),	
	@Config_SystemKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tsysConfig_System] (
	[SysConfigDatabaseName],
	[SysConfigMoviesIntroductionText]		
) VALUES (
	@SysConfigDatabaseName,
	@SysConfigMoviesIntroductionText	
)
 
SET @Config_SystemKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getSysConfig]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_getSysConfig]
 
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
select TOP 1
		SysConfigMoviesIntroductionText 
from 
		tsysConfig_System
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_UpdateSysConfig]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_UpdateSysConfig]
(
 
@Intro varchar(2000) 
 
)
AS
/*
    Author :Jessica
    Date :	3/28/2007
    Notes :	update information at tsysConfig_System
    Change Log :3/28/2007 JC sproc created
*/
 
 
	UPDATE			tsysConfig_System
	SET				SysConfigMoviesIntroductionText = @Intro
	WHERE			SysConfigKey = 1 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Version_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Version_Delete]
	@VersionKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tsysVersion]
WHERE
	[VersionKey] = @VersionKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Version_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Version_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	VersionKey,
	Version,
	VersionStatus,
	VersionWarning,
	VersionCreateTS,
	VersionApplicationName,
	VersionFolder,
	VersionNotes,
	VersionLastLaunched	
	
FROM
	[dbo].[tsysVersion]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Version_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Version_Insert]
	@Version nvarchar (50),
	@VersionStatus nvarchar (50),
	@VersionWarning nvarchar (255),
	@VersionApplicationName nvarchar (50),
	@VersionFolder nvarchar (255),
	@VersionNotes nvarchar (255),
	@VersionLastLaunched datetime,	
	@VersionKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tsysVersion] (
	[Version],
	[VersionStatus],
	[VersionWarning],
	[VersionApplicationName],
	[VersionFolder],
	[VersionNotes],
	[VersionLastLaunched]		
) VALUES (
	@Version,
	@VersionStatus,
	@VersionWarning,
	@VersionApplicationName,
	@VersionFolder,
	@VersionNotes,
	@VersionLastLaunched	
)
 
SET @VersionKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Version_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Version_Update]
	@VersionKey int,
	@Version nvarchar (50),
	@VersionStatus nvarchar (50),
	@VersionWarning nvarchar (255),
	@VersionApplicationName nvarchar (50),
	@VersionFolder nvarchar (255),
	@VersionNotes nvarchar (255),
	@VersionLastLaunched datetime	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tsysVersion] SET
	
	[Version]  = @Version,
	[VersionStatus]  = @VersionStatus,
	[VersionWarning]  = @VersionWarning,
	[VersionApplicationName]  = @VersionApplicationName,
	[VersionFolder]  = @VersionFolder,
	[VersionNotes]  = @VersionNotes,
	[VersionLastLaunched]  = @VersionLastLaunched	
 
WHERE
	[VersionKey] = @VersionKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Version_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Version_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	VersionKey,
	Version,
	VersionStatus,
	VersionWarning,
	VersionCreateTS,
	VersionApplicationName,
	VersionFolder,
	VersionNotes,
	VersionLastLaunched	
	
FROM
	[dbo].[tsysVersion]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Version_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Version_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Version_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Version_Get]
	@VersionKey int
AS
BEGIN --Procedure	
 
SELECT
	VersionKey,
	Version,
	VersionStatus,
	VersionWarning,
	VersionCreateTS,
	VersionApplicationName,
	VersionFolder,
	VersionNotes,
	VersionLastLaunched	
	
FROM
	[dbo].[tsysVersion]
WHERE
	[VersionKey] = @VersionKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_MovieComment_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MovieComment_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MovieCommentKey,
	MovieKey,
	CommentText,
	PersonKey	
	
FROM
	[dbo].[tblMovieComment]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_MovieComment_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MovieComment_Get]
	@MovieCommentKey int
AS
BEGIN --Procedure	
 
SELECT
	MovieCommentKey,
	MovieKey,
	CommentText,
	PersonKey	
	
FROM
	[dbo].[tblMovieComment]
WHERE
	[MovieCommentKey] = @MovieCommentKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_MovieComment_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MovieComment_Delete]
	@MovieCommentKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblMovieComment]
WHERE
	[MovieCommentKey] = @MovieCommentKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_MovieComment_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_MovieComment_Insert]
	@MovieKey int,
	@CommentText varchar (1500),
	@PersonKey int,	
	@MovieCommentKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblMovieComment] (
	[MovieKey],
	[CommentText],
	[PersonKey]		
) VALUES (
	@MovieKey,
	@CommentText,
	@PersonKey	
)
 
SET @MovieCommentKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_MovieComment_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MovieComment_Update]
	@MovieCommentKey int,
	@MovieKey int,
	@CommentText varchar (1500),
	@PersonKey int	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblMovieComment] SET
	
	[MovieKey]  = @MovieKey,
	[CommentText]  = @CommentText,
	[PersonKey]  = @PersonKey	
 
WHERE
	[MovieCommentKey] = @MovieCommentKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MovieComment_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MovieComment_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_MovieComment_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MovieComment_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MovieCommentKey,
	MovieKey,
	CommentText,
	PersonKey	
	
FROM
	[dbo].[tblMovieComment]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movies]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE   Procedure [dbo].[jsp_Movies]
(
	@PersonKey INT, -- if not 0 a Person is selected otherwise from all
	@BestWorst INT = 0, -- if 0 don''t select either, 1=top 20 best score, 2 = top 20 worst score 
	@startDate datetime = NULL,
	@endDate datetime = NULL
)
AS
/*
    Author :	Steve Senter
    Date :	9/3/2004
    Notes :	Return information for Movies for display, limited by @WhereClause if passed
    Change Log : 9/3/2004 - SJS - Sproc created
	change: Jessica 5/25/2006 build where clause here 
	change: Jessica 3/20/2007- added more filter elements
 
FOR TESTING...
 
DECLARE
	@PersonKey INT, 
	@BestWorst INT,
	@startDate datetime,
	@endDate datetime
SELECT
	@PersonKey = 1,
	@BestWorst = 1,
	@StartDate = NULL,
	@EndDate = NULL
--*/
DECLARE @strSQL varchar(1200)
DECLARE @WhereClause varchar(1000)
 
SET @startDate = dbo.jfn_GetDateOnly(@startDate)
SET @endDate = dbo.jfn_GetDateOnly(@endDate)
 
SET @WhereClause = ''''
 
IF @PersonKey > 0 
BEGIN
	Set @WhereClause='' WHERE  MovieKey IN (SELECT MovieKey FROM txrfMoviePerson WHERE PersonKey = '' + STR( @PersonKey) + '') ''
END
 
ELSE
BEGIN
	SET @WhereClause = '' WHERE 1=1 ''
END 
 
IF NOT @startDate IS NULL OR  NOT @endDate IS NULL 
BEGIN
 
  SELECT 
 	@WhereClause = 
	CASE 
		WHEN ( @startDate IS NOT NULL AND @endDate IS NOT NULL ) THEN
			@WhereClause + '' AND dbo.jfn_GetDateOnly(MovieCreatedTime) BETWEEN '''''' + @StartDate +  '''''' AND '''''' + @endDate + '''''' '' 
		
		WHEN (@startDate IS NOT NULL) THEN
			@WhereClause + '' AND dbo.jfn_GetDateOnly(MovieCreatedTime) >= '''''' + @startDate + '''''' ''
		
		WHEN ( @endDate IS NOT NULL) THEN
			@WhereClause +  '' AND dbo.jfn_GetDateOnly(MovieCreatedTime) <= '''''' + @endDate + '''''' ''
	END
  
END
 
IF @BestWorst <> 0 
BEGIN
	SET @strSQL = ''SELECT TOP 20 * FROM jvw_AvgMovieScores ''
	
	SET @strSQL = @strSQL + @WhereClause + '' ORDER BY AVGScore ''
 
	IF @BestWorst <> 1 --ORDER DESC
	BEGIN
		SET @strSQL = @strSQL + '' DESC ''
	END
END
 
ELSE
BEGIN
	SET @strSQL = ''SELECT * FROM jvw_AvgMovieScores '' + @WhereClause
END
 
 
Exec(@strSQL)
--PRINT(@strSQL)
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_MovieComment_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create Procedure [dbo].[jsp_MovieComment_Delete]
	(@MoviePersonKey int)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Delete a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
*/
Delete from txrfMoviePerson
WHERE     (MoviePersonKey = @MoviePersonKey)
return @@ROWCOUNT
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_MovieComment_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE  Procedure [dbo].[jsp_MovieComment_Insert]
(
	
	@MovieKey int=NULL,
	@PersonKey int = NULL,
	@ScoreKey int = NULL,
	@Comment varchar(255) =NULL,
	@createDate datetime = NULL
)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Insert a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
*/
 
DECLARE @MoviePersonKey int
SET @MoviePersonKey = -1 
	insert txrfMoviePerson
	(
	MovieKey,
	PersonKey,
	ScoreKey,
	MoviePersonComment,
	MoviePersonCreateTS
	)
	values
	(
	@MovieKey,
	@PersonKey,
	@ScoreKey,
	@Comment,
	@createDate
 
	)
	select @MoviePersonKey = @@IDENTITY
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMovieORPersonList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE   Procedure [dbo].[jsp_getMovieORPersonList]
(
@PersonKey int = 0,
@MovieKey int = 0
)
 
AS
/*
    Author :	Jessica Chong
    Date :	3/21/2007
    Notes :	List of  Movies for display
    Change Log : 3/21/2007 - JC - Sproc created
	
 
*/
 
If @PersonKey <> 0 
BEGIN
select 
		-1 as Moviekey,
		 ''- Select a Movie -'' as MovieSortTitle
 from
		 tvalRating 
UNION 
SELECT 
		Moviekey,
		MovieSortTitle 
FROM 
		tblMovie 
 
Where MovieKey not in 
(Select MovieKey from txrfMoviePerson Where PersonKey = @PersonKey)
 ORDER BY MovieSortTitle
 
END 
 
IF @MovieKey <> 0 
Begin
select 
		-1 as Personkey,
		 ''- Select a Person -'' as PersonName
 from
		 tvalRating 
UNION 
SELECT 
		Personkey, 
		PersonName
FROM 
		tblperson 
 
Where PersonActive <> 0 and PersonKey not in 
(Select PersonKey from txrfMoviePerson Where MovieKey = @MovieKey)
 ORDER BY PersonName
 
END 
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_MovieComment_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_MovieComment_Update]
(
	@MoviePersonKey int,
	@MovieKey int=NULL,
	@PersonKey int = NULL,
	@ScoreKey int = NULL,
	@Comment varchar(255) =NULL,
	@createDate datetime = NULL	
)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Update a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
	
*/
Update txrfMoviePerson
	Set
	MovieKey  = @MovieKey,
	PersonKey = @PersonKey,
	ScoreKey = @ScoreKey,
	MoviePersonComment = @Comment,
	MoviePersonCreateTS = @createDate
	Where MoviePersonKey = @MoviePersonKey
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_InsertMovieComment]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[jsp_InsertMovieComment]
(
	@MovieKey int,
	@PersonKey int,
	@ScoreKey int,
	@CommentText varchar(500) = '''',
	@createDate smalldatetime 
 
)
AS
 
Declare @MoviePersonKey int
SET @MoviePersonKey = -1 
 
 
INSERT INTO [dbo].[txrfMoviePerson]
 (
	MovieKey,
	PersonKey,
	ScoreKey,
	MoviePersonComment,
	MoviePersonCreateTS
) 
VALUES (
 
	@MovieKey,
	@PersonKey,
	@ScoreKey,
	@CommentText,
	@createDate
	
)
 
SET @MoviePersonKey = SCOPE_IDENTITY()
SELECT @MoviePersonKey as MoviePersonKey
 
 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_UpdateMovieComment]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE PROCEDURE [dbo].[jsp_UpdateMovieComment]
(	
	@MoviePersonKey int,
	@MovieKey int,	
	@PersonKey int, 
	@ScoreKey int,
	@CommentText varchar(500)='''',
	@createDate smalldatetime
)
 
 
AS
 
UPDATE txrfMoviePerson
SET
	MovieKey = @MovieKey,
	PersonKey =  @PersonKey,
	ScoreKey = @ScoreKey,
	MoviePersonComment = @CommentText,
	MoviePersonCreateTS = @createDate
WHERE
MoviePersonKey = @MoviePersonKey 
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MoviePerson_Get]
	@MoviePersonKey int
AS
BEGIN --Procedure	
 
SELECT
	MoviePersonKey,
	MovieKey,
	PersonKey,
	ScoreKey,
	MoviePersonComment,
	MoviePersonCreateTS	
	
FROM
	[dbo].[txrfMoviePerson]
WHERE
	[MoviePersonKey] = @MoviePersonKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MoviePerson_Delete]
	@MoviePersonKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[txrfMoviePerson]
WHERE
	[MoviePersonKey] = @MoviePersonKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MoviePerson_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MoviePersonKey,
	MovieKey,
	PersonKey,
	ScoreKey,
	MoviePersonComment,
	MoviePersonCreateTS	
	
FROM
	[dbo].[txrfMoviePerson]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MoviePerson_Update]
	@MoviePersonKey int,
	@MovieKey int,
	@PersonKey int,
	@ScoreKey int,
	@MoviePersonComment varchar (255)	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[txrfMoviePerson] SET
	
	[MovieKey]  = @MovieKey,
	[PersonKey]  = @PersonKey,
	[ScoreKey]  = @ScoreKey,
	[MoviePersonComment]  = @MoviePersonComment	
 
WHERE
	[MoviePersonKey] = @MoviePersonKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_MoviePerson_Insert]
	@MovieKey int,
	@PersonKey int,
	@ScoreKey int,
	@MoviePersonComment varchar (255),	
	@MoviePersonKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[txrfMoviePerson] (
	[MovieKey],
	[PersonKey],
	[ScoreKey],
	[MoviePersonComment]		
) VALUES (
	@MovieKey,
	@PersonKey,
	@ScoreKey,
	@MoviePersonComment	
)
 
SET @MoviePersonKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_MoviePerson_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_MoviePerson_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_MoviePerson_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_MoviePerson_GetByIds]
 
	@ScoreKey int,
	@MovieKey int,
	@PersonKey int
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MoviePersonKey,
	MovieKey,
	PersonKey,
	ScoreKey,
	MoviePersonComment,
	MoviePersonCreateTS	
	
FROM
	[dbo].[txrfMoviePerson]
 
 
WHERE
	COALESCE(txrfMoviePerson.ScoreKey, 0) = COALESCE(@ScoreKey, txrfMoviePerson.ScoreKey, 0)	
AND
	COALESCE(txrfMoviePerson.MovieKey, 0) = COALESCE(@MovieKey, txrfMoviePerson.MovieKey, 0)	
AND
	COALESCE(txrfMoviePerson.PersonKey, 0) = COALESCE(@PersonKey, txrfMoviePerson.PersonKey, 0)	
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Person_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Person_Get]
	@PersonKey int
AS
BEGIN --Procedure	
 
SELECT
	PersonKey,
	PersonName,
	PersonUID,
	PersonPWD,
	PersonCreateTS,
	PersonActive	
	
FROM
	[dbo].[tblPerson]
WHERE
	[PersonKey] = @PersonKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Person_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Person_Delete]
	@PersonKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblPerson]
WHERE
	[PersonKey] = @PersonKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Person_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_Person_Update]
(
	@PersonKey int,
	@Name varchar(255),
    @UserID varchar(50) = NULL,
     @Password varchar(50)=NULL,
	@Active int = 1
)
AS
/*
    Author :	Jessica Chong
    Date :	3/22/2007
    Notes :	Insert a Movie record
    Change Log : 
*/
 
 
UPDATE tblPerson
	SET 
	PersonName = @Name,
	PersonUID = @UserID,
	PersonPWD = @Password,
	PersonActive = @Active
 
WHERE PersonKey = @PersonKey
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Person_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Person_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	PersonKey,
	PersonName,
	PersonUID,
	PersonPWD,
	PersonCreateTS,
	PersonActive	
	
FROM
	[dbo].[tblPerson]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Person_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Person_Insert]
	@PersonName varchar (255),
	@PersonUID varchar (50),
	@PersonPWD varchar (50),
	@PersonActive int,	
	@PersonKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblPerson] (
	[PersonName],
	[PersonUID],
	[PersonPWD],
	[PersonActive]		
) VALUES (
	@PersonName,
	@PersonUID,
	@PersonPWD,
	@PersonActive	
)
 
SET @PersonKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Person_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Person_Update]
	@PersonKey int,
	@PersonName varchar (255),
	@PersonUID varchar (50),
	@PersonPWD varchar (50),
	@PersonActive int	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblPerson] SET
	
	[PersonName]  = @PersonName,
	[PersonUID]  = @PersonUID,
	[PersonPWD]  = @PersonPWD,
	[PersonActive]  = @PersonActive	
 
WHERE
	[PersonKey] = @PersonKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Person_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Person_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Person_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Person_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	PersonKey,
	PersonName,
	PersonUID,
	PersonPWD,
	PersonCreateTS,
	PersonActive	
	
FROM
	[dbo].[tblPerson]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Test_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test_Get]
	@TestKey int
AS
BEGIN --Procedure	
 
SELECT
	TestKey,
	GUID	
	
FROM
	[dbo].[tblTest]
WHERE
	[TestKey] = @TestKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Test_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test_Delete]
	@TestKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblTest]
WHERE
	[TestKey] = @TestKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Test_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	TestKey,
	GUID	
	
FROM
	[dbo].[tblTest]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Test_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	TestKey,
	GUID	
	
FROM
	[dbo].[tblTest]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Test_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test_Update]
	@TestKey int,
	@GUID uniqueidentifier	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblTest] SET
	
	[GUID]  = @GUID	
 
WHERE
	[TestKey] = @TestKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Test_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Test_Insert]
	@GUID uniqueidentifier,	
	@TestKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblTest] (
	[GUID]		
) VALUES (
	@GUID	
)
 
SET @TestKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_Get]
	@ContactPWDChangeRequestKey int
AS
BEGIN --Procedure	
 
SELECT
	ContactPWDChangeRequestKey,
	ContactKey,
	GUID,
	RequestDateTime	
	
FROM
	[dbo].[tblContactPWDChangeRequest]
WHERE
	[ContactPWDChangeRequestKey] = @ContactPWDChangeRequestKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_Delete]
	@ContactPWDChangeRequestKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblContactPWDChangeRequest]
WHERE
	[ContactPWDChangeRequestKey] = @ContactPWDChangeRequestKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ContactPWDChangeRequestKey,
	ContactKey,
	GUID,
	RequestDateTime	
	
FROM
	[dbo].[tblContactPWDChangeRequest]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_Insert]
	@ContactKey int,
	@GUID uniqueidentifier,
	@RequestDateTime datetime,	
	@ContactPWDChangeRequestKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblContactPWDChangeRequest] (
	[ContactKey],
	[GUID],
	[RequestDateTime]		
) VALUES (
	@ContactKey,
	@GUID,
	@RequestDateTime	
)
 
SET @ContactPWDChangeRequestKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_Update]
	@ContactPWDChangeRequestKey int,
	@ContactKey int,
	@GUID uniqueidentifier,
	@RequestDateTime datetime	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblContactPWDChangeRequest] SET
	
	[ContactKey]  = @ContactKey,
	[GUID]  = @GUID,
	[RequestDateTime]  = @RequestDateTime	
 
WHERE
	[ContactPWDChangeRequestKey] = @ContactPWDChangeRequestKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_ContactPWDChangeRequest_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_ContactPWDChangeRequest_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_ContactPWDChangeRequest_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_ContactPWDChangeRequest_GetByIds]
 
	@ContactPWDChangeRequestKey int
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ContactPWDChangeRequestKey,
	ContactKey,
	GUID,
	RequestDateTime	
	
FROM
	[dbo].[tblContactPWDChangeRequest]
 
 
WHERE
	COALESCE(tblContactPWDChangeRequest.ContactPWDChangeRequestKey, 0) = COALESCE(@ContactPWDChangeRequestKey, tblContactPWDChangeRequest.ContactPWDChangeRequestKey, 0)	
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Contact_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Contact_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Contact_Get]
	@ContactKey int
AS
BEGIN --Procedure	
 
SELECT
	ContactKey	
	
FROM
	[dbo].[tblContact]
WHERE
	[ContactKey] = @ContactKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Contact_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Contact_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Contact_Delete]
	@ContactKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblContact]
WHERE
	[ContactKey] = @ContactKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Contact_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Contact_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Contact_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ContactKey	
	
FROM
	[dbo].[tblContact]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Contact_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Contact_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Contact_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Contact_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	ContactKey	
	
FROM
	[dbo].[tblContact]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Movie_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Movie_Get]
	@MovieKey int
AS
BEGIN --Procedure	
 
SELECT
	MovieKey,
	MovieDisplayTitle,
	MovieSortTitle,
	RatingKey,
	MovieIMDBID,
	MovieCreateTS	
	
FROM
	[dbo].[tblMovie]
WHERE
	[MovieKey] = @MovieKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Movie_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Movie_Delete]
	@MovieKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblMovie]
WHERE
	[MovieKey] = @MovieKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Movie_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Movie_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MovieKey,
	MovieDisplayTitle,
	MovieSortTitle,
	RatingKey,
	MovieIMDBID,
	MovieCreateTS	
	
FROM
	[dbo].[tblMovie]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getAMovieTest]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
 
CREATE  Procedure [dbo].[jsp_getAMovieTest]
(
 
@MovieKey int,
@MovieKeyName varchar(255)
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
 
 
select 
		MovieKey,
		MovieDisplayTitle, MovieSortTitle,
		ISNULL(MovieIMDBID, '''') as IMDBID,
		ISNULL(RatingValue,''Unrated'') as RatingValue,
		ISNULL(tblMovie.RatingKey, 6) as RatingKey
 
from 
		tblMovie LEFT OUTER JOIN tvalRating 
		on tblMovie.RatingKey = tvalRating.RatingKey 
 
where @MovieKeyName = @MovieKey
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zzjsp_Movie_AddUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[zzjsp_Movie_AddUpdate] 
	@MovieKey int OUTPUT,
	@MovieDisplayTitle varchar(255),
	@MovieSortTitle varchar(255),
	@MovieIMDBID varchar(255),
	@RatingKey int
/*
    Author :	Steve Senter
    Date :	8/6/2003
    Issues :	NOT CURRENTLY USED 
    Notes :	Insert or Update a Movie record
    Change Log : 8/6/2003 - SJS - Sproc created
*/
AS
 
If (@MovieKey = -1)	--New Record
    Begin
	Insert into tblMovie (MovieDisplayTitle, MovieSortTitle, MovieIMDBID, RatingKey)
	Values (@MovieDisplayTitle, @MovieSortTitle, @MovieIMDBID, @RatingKey)
	set @MovieKey = @@Identity
    End
 
Else
    Begin
	Update tblMovie
	Set MovieDisplayTitle = @MovieDisplayTitle,
		MovieSortTitle = @MovieSortTitle,
		MovieIMDBID = @MovieIMDBID,
		RatingKey = @RatingKey
	Where MovieKey = @MovieKey
    End' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movies_get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jxp_Movies_get]
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT * from tblMovie
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_Movie]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author :	Steve Senter
    Date :	7/14/2003
    Issues :	none 
    Notes :	Summary data for Movie
    Change Log : 7/18/2003 SJS Changed MovieTitle to MovieDisplayTitle and added MovieSortTitle
		7/14/2003 - SJS - View created
*/
CREATE VIEW [dbo].[jvw_Movie]
AS
SELECT     MovieKey, MovieDisplayTitle, MovieSortTitle, ISNULL(RatingKey, - 1) AS RatingKey, ISNULL(MovieIMDBID, '''') AS MovieIMDBID, MovieCreateTS
FROM         dbo.tblMovie
 
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_SelectMovies]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[jsp_SelectMovies]
AS
/*
    Author :	Jessica Chong
    Date :	3/20/2007
    Notes :	this limits the list to active people
    Change Log :3/20/2007 - JC - created sproc
	 
*/
 
 
SELECT 
		 0 as Moviekey,
		 ''-- Select a Movie --'' as MovieSortTitle
FROM 
		tvalRating
 
 UNION 
 
SELECT 
		Moviekey,
		MovieSortTitle 
FROM 
		tblMovie 
ORDER BY 
		MovieSortTitle
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jfn_GetPersonLastMovieTitle]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE  FUNCTION [dbo].[jfn_GetPersonLastMovieTitle] (@PersonKey int)  
RETURNS varchar(500)  AS  
BEGIN 
/*
    Author : Steve Senter
    Date :7/11/2003
    Known Issues : none 
    Notes : Function to return the title of the last movie reviewed for the passed PersonKey
    Input Parameters : PersonKey
    Change Log :  7/11/2003 -- SJS -- Function created
*/
 
DECLARE @Title as varchar(500)
 
	SELECT     @Title = dbo.tblMovie.MovieDisplayTitle
	FROM         dbo.txrfMoviePerson INNER JOIN
                      dbo.tblMovie ON dbo.txrfMoviePerson.MovieKey = dbo.tblMovie.MovieKey
	WHERE     (dbo.txrfMoviePerson.PersonKey = @PersonKey) AND (dbo.txrfMoviePerson.MoviePersonCreateTS =
                          (SELECT     MAX(moviepersoncreatets)
                            FROM          txrfmovieperson
                            WHERE      personkey = @PersonKey))	
 
	return @Title
END
' 
END
 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_UpdateMovie_JessyTesting]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create  Procedure [dbo].[jsp_UpdateMovie_JessyTesting]
(
 
@MovieKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	testing row update on blur
    Change Log : 
*/
 
 
 
UPDATE tblMovie 
SET MovieKey = @MovieKey
WHERE MovieKey = @MovieKey  
	
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMovieByKey]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_getMovieByKey]
(
 
@MovieKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
 
select           * 
from				tblMovie 
where  MovieKey = @MovieKey' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMovie]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_getMovie]
(
 
@MovieKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
 
 
select 
		MovieKey,
		MovieDisplayTitle, MovieSortTitle,
		ISNULL(MovieIMDBID, '''') as IMDBID,
		ISNULL(RatingValue,''Unrated'') as RatingValue,
		ISNULL(tblMovie.RatingKey, 6) as RatingKey
 
from 
		tblMovie LEFT OUTER JOIN tvalRating 
		on tblMovie.RatingKey = tvalRating.RatingKey 
 
where MovieKey = @MovieKey
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movie_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_Movie_Update]
(
	@MovieKey int,
	@MovieDisplayTitle varchar(300) = null,
	@MovieSortTitle varchar(300) = null,
	@RatingKey int = NULL,
	@MovieIMDBID varchar(100) = null
)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Update a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
*/
	Update tblMovie
	Set
	MovieDisplayTitle = @MovieDisplayTitle,
	MovieSortTitle = @MovieSortTitle,
	RatingKey = @RatingKey,
	MovieIMDBID = @MovieIMDBID
	Where MovieKey = @MovieKey
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movie]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_Movie]
(
	@MovieKey int,
	@MovieDisplayTitle varchar(300) OUTPUT,
	@MovieSortTitle varchar(300) OUTPUT,
	@RatingKey int OUTPUT,
	@MovieIMDBID varchar(100) OUTPUT
)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Return information about a Movie for display
    Change Log : 9/1/2004 - SJS - Sproc created
*/
 
	Select
	@MovieDisplayTitle = MovieDisplayTitle,
	@MovieSortTitle = MovieSortTitle,
	@RatingKey = RatingKey,
	@MovieIMDBID = MovieIMDBID
	From tblMovie
	Where MovieKey = @MovieKey' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Movie_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Movie_Update]
	@MovieKey int,
	@MovieDisplayTitle varchar (255),
	@MovieSortTitle varchar (255),
	@RatingKey int,
	@MovieIMDBID varchar (50)	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblMovie] SET
	
	[MovieDisplayTitle]  = @MovieDisplayTitle,
	[MovieSortTitle]  = @MovieSortTitle,
	[RatingKey]  = @RatingKey,
	[MovieIMDBID]  = @MovieIMDBID	
 
WHERE
	[MovieKey] = @MovieKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Movie_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Movie_GetByIds]
 
	@RatingKey int
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	MovieKey,
	MovieDisplayTitle,
	MovieSortTitle,
	RatingKey,
	MovieIMDBID,
	MovieCreateTS	
	
FROM
	[dbo].[tblMovie]
 
 
WHERE
	COALESCE(tblMovie.RatingKey, 0) = COALESCE(@RatingKey, tblMovie.RatingKey, 0)	
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movie_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_Movie_Delete]
	(@MovieKey int)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Delete a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
*/
Delete from tblMovie
WHERE     (MovieKey = @MovieKey)
return @@ROWCOUNT' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_Movie_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE  Procedure [dbo].[jsp_Movie_Insert]
(
	@MovieDisplayTitle varchar(300) = null,
	@MovieSortTitle varchar(300) = null,
	@RatingKey int = 7,
	@MovieIMDBID varchar(100) = null
)
AS
/*
    Author :	Steve Senter
    Date :	9/1/2004
    Notes :	Insert a Movie record
    Change Log : 9/1/2004 - SJS - Sproc created
*/
 
DECLARE @MovieKey int
SEt @MovieKey = -1 
Select @MovieKey = MovieKey 
from tblMovie 
where MovieDisplayTitle like @MovieDisplayTitle
IF @MovieKey = -1 
BEGIN
	insert tblMovie
	(
	MovieDisplayTitle,
	MovieSortTitle,
	RatingKey,
	MovieIMDBID
	)
	values
	(
	@MovieDisplayTitle,
	@MovieSortTitle,
	@RatingKey,
	@MovieIMDBID
	)
END 
 
SET @MovieKey = @@IDENTITY 
SELECT @MovieKey as MovieKey
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Movie_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Movie_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Movie_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Movie_Insert]
	@MovieDisplayTitle varchar (255),
	@MovieSortTitle varchar (255),
	@RatingKey int,
	@MovieIMDBID varchar (50),	
	@MovieKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblMovie] (
	[MovieDisplayTitle],
	[MovieSortTitle],
	[RatingKey],
	[MovieIMDBID]		
) VALUES (
	@MovieDisplayTitle,
	@MovieSortTitle,
	@RatingKey,
	@MovieIMDBID	
)
 
SET @MovieKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Test2_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Test2_Insert]
	@MovieKey int,
	@Test1 varchar (255),	
	@Test2Key int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblTest2] (
	[MovieKey],
	[Test1]		
) VALUES (
	@MovieKey,
	@Test1	
)
 
SET @Test2Key = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Test2_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test2_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	Test2Key,
	MovieKey,
	Test1	
	
FROM
	[dbo].[tblTest2]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Test2_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test2_Get]
	@Test2Key int
AS
BEGIN --Procedure	
 
SELECT
	Test2Key,
	MovieKey,
	Test1	
	
FROM
	[dbo].[tblTest2]
WHERE
	[Test2Key] = @Test2Key
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Test2_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test2_Delete]
	@Test2Key int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblTest2]
WHERE
	[Test2Key] = @Test2Key
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Test2_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test2_GetByIds]
 
	@MovieKey int
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	Test2Key,
	MovieKey,
	Test1	
	
FROM
	[dbo].[tblTest2]
 
 
WHERE
	COALESCE(tblTest2.MovieKey, 0) = COALESCE(@MovieKey, tblTest2.MovieKey, 0)	
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Test2_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Test2_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Test2_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Test2_Update]
	@Test2Key int,
	@MovieKey int,
	@Test1 varchar (255)	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblTest2] SET
	
	[MovieKey]  = @MovieKey,
	[Test1]  = @Test1	
 
WHERE
	[Test2Key] = @Test2Key
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_Rating_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_Rating_Insert]
	@RatingValue varchar (50),
	@RatingSeq int,	
	@RatingKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tvalRating] (
	[RatingValue],
	[RatingSeq]		
) VALUES (
	@RatingValue,
	@RatingSeq	
)
 
SET @RatingKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_Rating_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Rating_Get]
	@RatingKey int
AS
BEGIN --Procedure	
 
SELECT
	RatingKey,
	RatingValue,
	RatingSeq,
	RatingCreateTS	
	
FROM
	[dbo].[tvalRating]
WHERE
	[RatingKey] = @RatingKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_Rating_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Rating_Delete]
	@RatingKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tvalRating]
WHERE
	[RatingKey] = @RatingKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Rating_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Rating_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	RatingKey,
	RatingValue,
	RatingSeq,
	RatingCreateTS	
	
FROM
	[dbo].[tvalRating]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getRatings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create  Procedure [dbo].[jsp_getRatings]
 
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
select  
		-1 as ratingkey,
		 ''- Select a Rating -'' as ratingvalue, 
		0 as ratingseq
 from 
		tvalRating 
UNION 
SELECT 
		ratingkey, 
		ratingvalue, 
		ratingseq 
FROM 
		tvalrating 
ORDER BY 
		ratingseq' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_Rating_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Rating_Update]
	@RatingKey int,
	@RatingValue varchar (50),
	@RatingSeq int	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tvalRating] SET
	
	[RatingValue]  = @RatingValue,
	[RatingSeq]  = @RatingSeq	
 
WHERE
	[RatingKey] = @RatingKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_Rating_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_Rating_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_Rating_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_Rating_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	RatingKey,
	RatingValue,
	RatingSeq,
	RatingCreateTS	
	
FROM
	[dbo].[tvalRating]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_Get]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Get.cst
-- Procedure Name: [dbo].[jxp_PersonTest_Get]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_PersonTest_Get]
	@PersonTestKey int
AS
BEGIN --Procedure	
 
SELECT
	PersonTestKey,
	FirstName,
	LastName,
	CarMake,
	CarModel	
	
FROM
	[dbo].[tblPersonTest]
WHERE
	[PersonTestKey] = @PersonTestKey
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_Delete]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Delete.cst
-- Procedure Name: [dbo].[jxp_PersonTest_Delete]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_PersonTest_Delete]
	@PersonTestKey int
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
 
SET NOCOUNT ON
 
DELETE FROM [dbo].[tblPersonTest]
WHERE
	[PersonTestKey] = @PersonTestKey
 
END --Procedure
--endregion
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_GetAll]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_PersonTest_GetAll]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_PersonTest_GetAll]
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	PersonTestKey,
	FirstName,
	LastName,
	CarMake,
	CarModel	
	
FROM
	[dbo].[tblPersonTest]
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_Insert]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Insert.cst
-- Procedure Name: [dbo].[jxp_PersonTest_Insert]
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[jxp_PersonTest_Insert]
	@FirstName varchar (255),
	@LastName varchar (255),
	@CarMake varchar (255),
	@CarModel varchar (255),	
	@PersonTestKey int OUTPUT	
	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
INSERT INTO [dbo].[tblPersonTest] (
	[FirstName],
	[LastName],
	[CarMake],
	[CarModel]		
) VALUES (
	@FirstName,
	@LastName,
	@CarMake,
	@CarModel	
)
 
SET @PersonTestKey = SCOPE_IDENTITY()
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_Update]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-Update.cst
-- Procedure Name: [dbo].[jxp_PersonTest_Update]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_PersonTest_Update]
	@PersonTestKey int,
	@FirstName varchar (255),
	@LastName varchar (255),
	@CarMake varchar (255),
	@CarModel varchar (255)	
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE [dbo].[tblPersonTest] SET
	
	[FirstName]  = @FirstName,
	[LastName]  = @LastName,
	[CarMake]  = @CarMake,
	[CarModel]  = @CarModel	
 
WHERE
	[PersonTestKey] = @PersonTestKey
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jxp_PersonTest_GetByIds]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--region [dbo].[jxp_PersonTest_GetByIds]
 
------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Steve Reynolds using CodeSmith 3.2.7
-- Template:       JST-CRUD-Sproc-GetAll.cst
-- Procedure Name: [dbo].[jxp_PersonTest_GetByIds]
------------------------------------------------------------------------------------------------------------------------
 
CREATE PROCEDURE [dbo].[jxp_PersonTest_GetByIds]
 
 
 
AS
BEGIN --Procedure	
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT
	PersonTestKey,
	FirstName,
	LastName,
	CarMake,
	CarModel	
	
FROM
	[dbo].[tblPersonTest]
 
 
 
END --Procedure
--endregion
 
 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[jvw_AvgReviewers]'))
EXEC dbo.sp_executesql @statement = N'/*
    Author :	Steve Senter
    Date :	7/11/2003
    Issues :	none 
    Notes :	Summary data for Reviewers
    Change Log : 7/11/2003 - SJS - View created
*/
CREATE VIEW [dbo].[jvw_AvgReviewers]
AS
SELECT     TOP 100 PERCENT dbo.tblPerson.PersonKey, dbo.tblPerson.PersonName AS Reviewer, AVG(ISNULL(dbo.tvalScore.ScoreValue, 0)) AS AvgScore, 
                      COUNT(dbo.txrfMoviePerson.MoviePersonKey) AS Reviews, ISNULL(dbo.jfn_GetPersonLastMovieTitle(dbo.txrfMoviePerson.PersonKey), ''n/a'') 
                      AS LastMovieTitle, dbo.jfn_GetPersonLastMovieDate(dbo.txrfMoviePerson.PersonKey) AS LastMovieDate
FROM         dbo.txrfMoviePerson RIGHT OUTER JOIN
                      dbo.tblPerson ON dbo.txrfMoviePerson.PersonKey = dbo.tblPerson.PersonKey LEFT OUTER JOIN
                      dbo.tvalScore ON dbo.txrfMoviePerson.ScoreKey = dbo.tvalScore.ScoreKey
WHERE     (dbo.tblPerson.PersonActive <> 0)
GROUP BY dbo.tblPerson.PersonKey, dbo.tblPerson.PersonName, ISNULL(dbo.jfn_GetPersonLastMovieTitle(dbo.txrfMoviePerson.PersonKey), ''n/a''), 
                      dbo.jfn_GetPersonLastMovieDate(dbo.txrfMoviePerson.PersonKey)
ORDER BY dbo.tblPerson.PersonName
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[30] 4[15] 2[25] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "txrfMoviePerson"
            Begin Extent = 
               Top = 13
               Left = 287
               Bottom = 128
               Right = 490
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tblPerson"
            Begin Extent = 
               Top = 10
               Left = 680
               Bottom = 125
               Right = 855
            End
            DisplayFlags = 280
            TopColumn = 3
         End
         Begin Table = "tvalScore"
            Begin Extent = 
               Top = 9
               Left = 25
               Bottom = 124
               Right = 194
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 12
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'jvw_AvgReviewers'
 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'jvw_AvgReviewers'
 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMoviesByPerson]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_getMoviesByPerson]
(
 
@PersonKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/22/2007
    Notes :	Return movies filtered by person
    Change Log : 
*/
 
 
Select     * 
from		 jvw_MovieScoreSummary
where	 PersonKey = @PersonKey
Order By EntryDate desc
 
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMovieComment]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  Procedure [dbo].[jsp_getMovieComment]
(
 
@MovieKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/21/2007
    Notes :	Return general Information
    Change Log : 
*/
 
 
Select     * 
from     jvw_MovieScoreSummary
where MovieKey = @MovieKey
 Order By EntryDate desc, PersonName
 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jsp_getMoviesByReviewer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[jsp_getMoviesByReviewer]
(
 
@PersonKey int
)
AS
/*
    Author :Jessica Chong
    Date :	03/22/2007
    Notes :	Return movies filtered by person
    Change Log : 
*/
 
 
Select			* 
from			jvw_ReviewerSummary
where		PersonKey = @PersonKey
Order By   EntryDate desc
 
 
 
' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblContactPWDChangeRequest_tblContactPWDChangeRequest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblContactPWDChangeRequest]'))
ALTER TABLE [dbo].[tblContactPWDChangeRequest]  WITH CHECK ADD  CONSTRAINT [FK_tblContactPWDChangeRequest_tblContactPWDChangeRequest] FOREIGN KEY([ContactPWDChangeRequestKey])
REFERENCES [dbo].[tblContactPWDChangeRequest] ([ContactPWDChangeRequestKey])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_txrfMoviePerson_tblMovie]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfMoviePerson]'))
ALTER TABLE [dbo].[txrfMoviePerson]  WITH NOCHECK ADD  CONSTRAINT [FK_txrfMoviePerson_tblMovie] FOREIGN KEY([MovieKey])
REFERENCES [dbo].[tblMovie] ([MovieKey])
GO
ALTER TABLE [dbo].[txrfMoviePerson] CHECK CONSTRAINT [FK_txrfMoviePerson_tblMovie]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_txrfMoviePerson_tblPerson]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfMoviePerson]'))
ALTER TABLE [dbo].[txrfMoviePerson]  WITH CHECK ADD  CONSTRAINT [FK_txrfMoviePerson_tblPerson] FOREIGN KEY([PersonKey])
REFERENCES [dbo].[tblPerson] ([PersonKey])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_txrfMoviePerson_tvalScore]') AND parent_object_id = OBJECT_ID(N'[dbo].[txrfMoviePerson]'))
ALTER TABLE [dbo].[txrfMoviePerson]  WITH NOCHECK ADD  CONSTRAINT [FK_txrfMoviePerson_tvalScore] FOREIGN KEY([ScoreKey])
REFERENCES [dbo].[tvalScore] ([ScoreKey])
GO
ALTER TABLE [dbo].[txrfMoviePerson] CHECK CONSTRAINT [FK_txrfMoviePerson_tvalScore]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild2_tblParent1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild2]'))
ALTER TABLE [dbo].[tblChild2]  WITH CHECK ADD  CONSTRAINT [FK_tblChild2_tblParent1] FOREIGN KEY([Parent1Key])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
ON UPDATE SET DEFAULT
ON DELETE SET DEFAULT
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild1_tblParent1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild1]'))
ALTER TABLE [dbo].[tblChild1]  WITH CHECK ADD  CONSTRAINT [FK_tblChild1_tblParent1] FOREIGN KEY([Parent1Key_1])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblChild1_tblParent11]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblChild1]'))
ALTER TABLE [dbo].[tblChild1]  WITH CHECK ADD  CONSTRAINT [FK_tblChild1_tblParent11] FOREIGN KEY([Parent1Key_2])
REFERENCES [dbo].[tblParent1] ([Parent1Key])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTest2_tblMovie]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTest2]'))
ALTER TABLE [dbo].[tblTest2]  WITH CHECK ADD  CONSTRAINT [FK_tblTest2_tblMovie] FOREIGN KEY([MovieKey])
REFERENCES [dbo].[tblMovie] ([MovieKey])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMovie_tvalRating]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMovie]'))
ALTER TABLE [dbo].[tblMovie]  WITH NOCHECK ADD  CONSTRAINT [FK_tblMovie_tvalRating] FOREIGN KEY([RatingKey])
REFERENCES [dbo].[tvalRating] ([RatingKey])
GO
ALTER TABLE [dbo].[tblMovie] CHECK CONSTRAINT [FK_tblMovie_tvalRating]

Open in new window

steverey443

ASKER

OK. It looks like the information is available using sp_help.

But now I'm not sure how to run sp_help against the FK or contraint by name rather than just the table. Running it on the table does not return the information. Any ideas?

"When a foreign key is specified as the object of sp_help, the output result set contains the following columns."

http://msdn.microsoft.com/en-us/library/ms186973.aspx

Cascading Referential Constraints Catalog Information
Querying the sys.foreign_keys catalog view returns the following values that indicate the cascading referential constraint specified for a foreign key.

Value Description
0
NO ACTION

1
CASCADE

2
SET NULL

3
SET DEFAULT

The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and sp_foreignkeys return 0 when CASCADE, SET NULL, or SET DEFAULT is specified; and return 1 when NO ACTION is specified or is the default.

When a foreign key is specified as the object of sp_help, the output result set contains the following columns.

Column name Data type Description
delete_action
nvarchar(9)
Indicates whether the delete action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable).

update_action
nvarchar(9)
Indicates whether the update action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable).

steverey443

ASKER

I found another way to get this.

select
      [name],delete_referential_action_desc, update_referential_action_desc
from
      sys.foreign_keys
where [name] = 'FK_tblChild2_tblParent1'
;

name      delete_referential_action_desc      update_referential_action_desc
FK_tblChild2_tblParent1      SET_DEFAULT      CASCADE

Tell me how to use a Foreign Key as an arguement for sp_help and win the points!

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

steverey443

ASKER

Thanks Daniel,

I'll accept this as the answer. It's basically the same thing I had come up with.

I was unable to get the "foreign key is specified as the object of sp_help" to work as you had stated even though the following link from MS seems to indicate that it is possible.

"When a foreign key is specified as the object of sp_help, the output result set contains the following columns."

http://msdn.microsoft.com/en-us/library/ms186973.aspx

Thanks for taking the time to help out. I can now add a property to each items class in the middle tier for this setting which is the goal!

How is the value found for &quot;Update Rule&quot; or &quot;Update Rule&quot; on a foreign key

How is the value found for "Update Rule" or "Update Rule" on a foreign key