Solved

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

Posted on 2008-10-23
7
645 Views
Last Modified: 2008-10-30
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

0
Comment
Question by:steverey443
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22789458
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

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22789465
The O.Name should be compared to your @TableName variable ... I posted w/ my hard-coded test value.
0
 

Author Comment

by:steverey443
ID: 22791893
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

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:steverey443
ID: 22791908
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).
 
0
 

Author Comment

by:steverey443
ID: 22792140
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!
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22795449
You can't use a Foreign Key as an argument for sp_help.

But to adapt your code so you can get all the FK's for the table:


select
      O.Name as Tablename, F.name as KeyName, delete_referential_action_desc, update_referential_action_desc
from sys.objects O  inner join sys.foreign_keys f on O.object_id = f.referenced_object_id
Where O.Name = @TableName
0
 

Author Comment

by:steverey443
ID: 22846049
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!

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now