USE dbxxxxxxxxx
----------------------------------------------------------------------------------
-- Create table: vgViziGenRoot
----------------------------------------------------------------------------------
--The following is too dangerous - but reinstate it if you really want to
--if exists(select * from sysobjects where name = 'vgViziGenRoot' and type = 'U')
-- drop table vgViziGenRoot
--go
CREATE TABLE [dbo].[vgViziGenRoot]
(
    [IdViziGenRoot] [int] NOT NULL
    ,[lVisitsDefault] [bigint] NOT NULL
    ,[CCN] [int] NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------------------------------------------
-- Create table: vgUser
----------------------------------------------------------------------------------
--The following is very dangerous - but reinstate it if you really want to
--if exists(select * from sysobjects where name = 'vgUser' and type = 'U')
-- drop table vgUser
--go
CREATE TABLE [dbo].[vgUser]
(
    [IdUser] [int] IDENTITY (1, 1) NOT NULL
    ,[dtDateRegistered] [datetime] NOT NULL
    ,[dtTimestampLastLogin] [datetime]
    ,[dtTimestampLockout] [datetime]
    ,[nFailedPasswordCount] [int]
    ,[sUserName] [varchar] (20) UNIQUE NOT NULL
    ,[sEmailAddress] [varchar] (100) NOT NULL
    ,[arbPassword] [varbinary] (100) NOT NULL
    ,[sSecurityQuestion] [varchar] (50) NOT NULL
    ,[sSecurityAnswer] [varchar] (50) NOT NULL
    ,[sEmailAddressNew] [varchar] (100)
    ,[arbPasswordNew] [varbinary] (100) NOT NULL
    ,[sTitle] [varchar] (10)
    ,[sFirstName] [varchar] (30)
    ,[sSurname] [varchar] (50)
    ,[sCompany] [varchar] (50)
    ,[sLocation] [varchar] (50)
    ,[sJobTitle] [varchar] (50)
    ,[sTelNo] [varchar] (50)
    ,[sUmlTool] [varchar] (50)
    ,[CCN] [int] NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------------------------------------------
-- Create table: vgProduct
----------------------------------------------------------------------------------
--The following is very dangerous - but reinstate it if you really want to
--if exists(select * from sysobjects where name = 'vgProduct' and type = 'U')
-- drop table vgProduct
--go
CREATE TABLE [dbo].[vgProduct]
(
    [IdProduct] [int] IDENTITY (1, 1) NOT NULL
    ,[lProductCode] [bigint] UNIQUE NOT NULL
    ,[dtDateStartSubscription] [datetime] NOT NULL
    ,[dtDateEndSubscription] [datetime] NOT NULL
    ,[dtDateStartDownload] [datetime] NOT NULL
    ,[dtDateEndDownload] [datetime] NOT NULL
    ,[Application] [smallint] NOT NULL
    ,[Version] [smallint] NOT NULL
    ,[Beta] [smallint] NOT NULL
    ,[sProductName] [varchar] (50) UNIQUE NOT NULL
    ,[sDownloadFileName] [varchar] (256) NOT NULL
    ,[CCN] [int] NOT NULL
) ON [PRIMARY]
GO
----------------------------------------------------------------------------------
-- Create non-unique Index on table vgUser using column EmailAddress
----------------------------------------------------------------------------------
if exists(select * from sysindexes where name = 'Idx_vgUser_EmailAddress')
begin
    drop index vgUser.Idx_vgUser_EmailAddress
end
else
begin
CREATE INDEX [Idx_vgUser_EmailAddress]
    ON [dbo].[vgUser] ([sEmailAddress])
    ON [PRIMARY]
end
GO
----------------------------------------------------------------------------------
-- Stored Procedure: Insert into vgViziGenRoot
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgViziGenRoot_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgViziGenRoot_Insert]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgViziGenRoot_Insert
(
    @IdViziGenRoot int
    ,@lVisitsDefault bigint
)
AS
INSERT INTO vgViziGenRoot
(
    CCN
    ,IdViziGenRoot
    ,lVisitsDefault
)
VALUES
(
    1
    ,@IdViziGenRoot
    ,@lVisitsDefault
)
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgViziGenRoot_Insert
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Insert into vgUser
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_Insert]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_Insert
(
    @IdUser int output
    ,@dtDateRegistered datetime
    ,@dtTimestampLastLogin datetime
    ,@dtTimestampLockout datetime
    ,@nFailedPasswordCount int
    ,@sUserName varchar (20)
    ,@sEmailAddress varchar (100)
    ,@arbPassword varbinary (100)
    ,@sSecurityQuestion varchar (50)
    ,@sSecurityAnswer varchar (50)
    ,@sEmailAddressNew varchar (100)
    ,@arbPasswordNew varbinary (100)
    ,@sTitle varchar (10)
    ,@sFirstName varchar (30)
    ,@sSurname varchar (50)
    ,@sCompany varchar (50)
    ,@sLocation varchar (50)
    ,@sJobTitle varchar (50)
    ,@sTelNo varchar (50)
    ,@sUmlTool varchar (50)
)
AS
INSERT INTO vgUser
(
    CCN
    ,sUserName
    ,sEmailAddress
    ,arbPassword
    ,dtDateRegistered
    ,sSecurityQuestion
    ,sSecurityAnswer
    ,sEmailAddressNew
    ,arbPasswordNew
    ,sTitle
    ,sFirstName
    ,sSurname
    ,sCompany
    ,sLocation
    ,sJobTitle
    ,sTelNo
    ,sUmlTool
    ,dtTimestampLastLogin
    ,dtTimestampLockout
    ,nFailedPasswordCount
)
VALUES
(
    1
    ,@sUserName
    ,@sEmailAddress
    ,@arbPassword
    ,@dtDateRegistered
    ,@sSecurityQuestion
    ,@sSecurityAnswer
    ,@sEmailAddressNew
    ,@arbPasswordNew
    ,@sTitle
    ,@sFirstName
    ,@sSurname
    ,@sCompany
    ,@sLocation
    ,@sJobTitle
    ,@sTelNo
    ,@sUmlTool
    ,@dtTimestampLastLogin
    ,@dtTimestampLockout
    ,@nFailedPasswordCount
)
/* check for error in the insert */
if @@error = 0
begin
    /* Set the out parameters */
    set @IdUser = @@identity
end
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_Insert
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Insert into vgProduct
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_Insert]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_Insert
(
    @IdProduct int output
    ,@lProductCode bigint
    ,@dtDateStartSubscription datetime
    ,@dtDateEndSubscription datetime
    ,@dtDateStartDownload datetime
    ,@dtDateEndDownload datetime
    ,@Application smallint
    ,@Version smallint
    ,@Beta smallint
    ,@sProductName varchar (50)
    ,@sDownloadFileName varchar (256)
)
AS
INSERT INTO vgProduct
(
    CCN
    ,lProductCode
    ,sProductName
    ,dtDateStartSubscription
    ,dtDateEndSubscription
    ,dtDateStartDownload
    ,dtDateEndDownload
    ,sDownloadFileName
    ,Application
    ,Version
    ,Beta
)
VALUES
(
    1
    ,@lProductCode
    ,@sProductName
    ,@dtDateStartSubscription
    ,@dtDateEndSubscription
    ,@dtDateStartDownload
    ,@dtDateEndDownload
    ,@sDownloadFileName
    ,@Application
    ,@Version
    ,@Beta
)
/* check for error in the insert */
if @@error = 0
begin
    /* Set the out parameters */
    set @IdProduct = @@identity
end
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_Insert
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Delete from vgViziGenRoot
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgViziGenRoot_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgViziGenRoot_Delete]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgViziGenRoot_Delete (
    @IdViziGenRoot int
)
AS
DELETE vgViziGenRoot
WHERE IdViziGenRoot = @IdViziGenRoot
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgViziGenRoot_Delete
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Delete from vgUser
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_Delete]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_Delete (
    @IdUser int
)
AS
DELETE vgUser
WHERE IdUser = @IdUser
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_Delete
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Delete from vgProduct
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_Delete]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_Delete (
    @IdProduct int
)
AS
DELETE vgProduct
WHERE IdProduct = @IdProduct
return @@error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_Delete
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Update vgViziGenRoot
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgViziGenRoot_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgViziGenRoot_Update]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgViziGenRoot_Update (
    @IdViziGenRoot int
    ,@lVisitsDefault bigint
    ,@CCN int
)
AS
UPDATE vgViziGenRoot
SET
    CCN = @CCN + 1
    ,lVisitsDefault = @lVisitsDefault
    WHERE IdViziGenRoot = @IdViziGenRoot AND CCN = @CCN
if (@@ROWCOUNT = 0)
BEGIN
    return -1 /* -1 = Another user has modified the row */
END
ELSE
BEGIN
    return @@error
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgViziGenRoot_Update
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Update vgUser
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_Update]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_Update (
    @IdUser int
    ,@dtDateRegistered datetime
    ,@dtTimestampLastLogin datetime
    ,@dtTimestampLockout datetime
    ,@nFailedPasswordCount int
    ,@sUserName varchar (20)
    ,@sEmailAddress varchar (100)
    ,@arbPassword varbinary (100)
    ,@sSecurityQuestion varchar (50)
    ,@sSecurityAnswer varchar (50)
    ,@sEmailAddressNew varchar (100)
    ,@arbPasswordNew varbinary (100)
    ,@sTitle varchar (10)
    ,@sFirstName varchar (30)
    ,@sSurname varchar (50)
    ,@sCompany varchar (50)
    ,@sLocation varchar (50)
    ,@sJobTitle varchar (50)
    ,@sTelNo varchar (50)
    ,@sUmlTool varchar (50)
    ,@CCN int
)
AS
UPDATE vgUser
SET
    CCN = @CCN + 1
    ,sUserName = @sUserName
    ,sEmailAddress = @sEmailAddress
    ,arbPassword = @arbPassword
    ,dtDateRegistered = @dtDateRegistered
    ,sSecurityQuestion = @sSecurityQuestion
    ,sSecurityAnswer = @sSecurityAnswer
    ,sEmailAddressNew = @sEmailAddressNew
    ,arbPasswordNew = @arbPasswordNew
    ,sTitle = @sTitle
    ,sFirstName = @sFirstName
    ,sSurname = @sSurname
    ,sCompany = @sCompany
    ,sLocation = @sLocation
    ,sJobTitle = @sJobTitle
    ,sTelNo = @sTelNo
    ,sUmlTool = @sUmlTool
    ,dtTimestampLastLogin = @dtTimestampLastLogin
    ,dtTimestampLockout = @dtTimestampLockout
    ,nFailedPasswordCount = @nFailedPasswordCount
    WHERE IdUser = @IdUser AND CCN = @CCN
if (@@ROWCOUNT = 0)
BEGIN
    return -1 /* -1 = Another user has modified the row */
END
ELSE
BEGIN
    return @@error
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_Update
TO dboxxxxxxxxx
----------------------------------------------------------------------------------
-- Stored Procedure: Update vgProduct
----------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_Update]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_Update (
    @IdProduct int
    ,@lProductCode bigint
    ,@dtDateStartSubscription datetime
    ,@dtDateEndSubscription datetime
    ,@dtDateStartDownload datetime
    ,@dtDateEndDownload datetime
    ,@Application smallint
    ,@Version smallint
    ,@Beta smallint
    ,@sProductName varchar (50)
    ,@sDownloadFileName varchar (256)
    ,@CCN int
)
AS
UPDATE vgProduct
SET
    CCN = @CCN + 1
    ,lProductCode = @lProductCode
    ,sProductName = @sProductName
    ,dtDateStartSubscription = @dtDateStartSubscription
    ,dtDateEndSubscription = @dtDateEndSubscription
    ,dtDateStartDownload = @dtDateStartDownload
    ,dtDateEndDownload = @dtDateEndDownload
    ,sDownloadFileName = @sDownloadFileName
    ,Application = @Application
    ,Version = @Version
    ,Beta = @Beta
    WHERE IdProduct = @IdProduct AND CCN = @CCN
if (@@ROWCOUNT = 0)
BEGIN
    return -1 /* -1 = Another user has modified the row */
END
ELSE
BEGIN
    return @@error
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_Update
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgViziGenRoot) via the Primary Key (IdViziGenRoot)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgViziGenRoot_LoadByIdViziGenRoot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgViziGenRoot_LoadByIdViziGenRoot]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgViziGenRoot_LoadByIdViziGenRoot
(
    @IdViziGenRoot int
)
 AS
SELECT CCN,IdViziGenRoot
    ,lVisitsDefault
FROM vgViziGenRoot (nolock)
WHERE (IdViziGenRoot = @IdViziGenRoot)
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgViziGenRoot_LoadByIdViziGenRoot
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgUser) via the Primary Key (IdUser)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_LoadByIdUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_LoadByIdUser]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_LoadByIdUser
(
    @IdUser int
)
 AS
SELECT CCN,IdUser
    ,sUserName
    ,sEmailAddress
    ,arbPassword
    ,dtDateRegistered
    ,sSecurityQuestion
    ,sSecurityAnswer
    ,sEmailAddressNew
    ,arbPasswordNew
    ,sTitle
    ,sFirstName
    ,sSurname
    ,sCompany
    ,sLocation
    ,sJobTitle
    ,sTelNo
    ,sUmlTool
    ,dtTimestampLastLogin
    ,dtTimestampLockout
    ,nFailedPasswordCount
FROM vgUser (nolock)
WHERE (IdUser = @IdUser)
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_LoadByIdUser
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgUser) via the non-Primary Key (UserName)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_LoadByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_LoadByUserName]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_LoadByUserName
(
    @sUserName varchar (20)
)
 AS
SELECT CCN,IdUser
    ,sUserName
    ,sEmailAddress
    ,arbPassword
    ,dtDateRegistered
    ,sSecurityQuestion
    ,sSecurityAnswer
    ,sEmailAddressNew
    ,arbPasswordNew
    ,sTitle
    ,sFirstName
    ,sSurname
    ,sCompany
    ,sLocation
    ,sJobTitle
    ,sTelNo
    ,sUmlTool
    ,dtTimestampLastLogin
    ,dtTimestampLockout
    ,nFailedPasswordCount
FROM vgUser (nolock)
WHERE (sUserName = @sUserName)
ORDER BY IdUser
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_LoadByUserName
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgUser) via the non-Primary Key (EmailAddress)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_LoadByEmailAddress]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_LoadByEmailAddress]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_LoadByEmailAddress
(
    @sEmailAddress varchar (100)
)
 AS
SELECT CCN,IdUser
    ,sUserName
    ,sEmailAddress
    ,arbPassword
    ,dtDateRegistered
    ,sSecurityQuestion
    ,sSecurityAnswer
    ,sEmailAddressNew
    ,arbPasswordNew
    ,sTitle
    ,sFirstName
    ,sSurname
    ,sCompany
    ,sLocation
    ,sJobTitle
    ,sTelNo
    ,sUmlTool
    ,dtTimestampLastLogin
    ,dtTimestampLockout
    ,nFailedPasswordCount
FROM vgUser (nolock)
WHERE (sEmailAddress = @sEmailAddress)
ORDER BY IdUser
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_LoadByEmailAddress
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying for all rows in table: vgUser
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgUser_Load]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgUser_Load]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgUser_Load
 AS
SELECT CCN,IdUser
    ,sUserName
    ,sEmailAddress
    ,arbPassword
    ,dtDateRegistered
    ,sSecurityQuestion
    ,sSecurityAnswer
    ,sEmailAddressNew
    ,arbPasswordNew
    ,sTitle
    ,sFirstName
    ,sSurname
    ,sCompany
    ,sLocation
    ,sJobTitle
    ,sTelNo
    ,sUmlTool
    ,dtTimestampLastLogin
    ,dtTimestampLockout
    ,nFailedPasswordCount
FROM vgUser (nolock)
ORDER BY IdUser
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgUser_Load
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgProduct) via the Primary Key (IdProduct)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_LoadByIdProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_LoadByIdProduct]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_LoadByIdProduct
(
    @IdProduct int
)
 AS
SELECT CCN,IdProduct
    ,lProductCode
    ,sProductName
    ,dtDateStartSubscription
    ,dtDateEndSubscription
    ,dtDateStartDownload
    ,dtDateEndDownload
    ,sDownloadFileName
    ,Application
    ,Version
    ,Beta
FROM vgProduct (nolock)
WHERE (IdProduct = @IdProduct)
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_LoadByIdProduct
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgProduct) via the non-Primary Key (ProductCode)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_LoadByProductCode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_LoadByProductCode]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_LoadByProductCode
(
    @lProductCode bigint
)
 AS
SELECT CCN,IdProduct
    ,lProductCode
    ,sProductName
    ,dtDateStartSubscription
    ,dtDateEndSubscription
    ,dtDateStartDownload
    ,dtDateEndDownload
    ,sDownloadFileName
    ,Application
    ,Version
    ,Beta
FROM vgProduct (nolock)
WHERE (lProductCode = @lProductCode)
ORDER BY IdProduct
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_LoadByProductCode
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying table (vgProduct) via the non-Primary Key (ProductName)
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_LoadByProductName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_LoadByProductName]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_LoadByProductName
(
    @sProductName varchar (50)
)
 AS
SELECT CCN,IdProduct
    ,lProductCode
    ,sProductName
    ,dtDateStartSubscription
    ,dtDateEndSubscription
    ,dtDateStartDownload
    ,dtDateEndDownload
    ,sDownloadFileName
    ,Application
    ,Version
    ,Beta
FROM vgProduct (nolock)
WHERE (sProductName = @sProductName)
ORDER BY IdProduct
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_LoadByProductName
TO dboxxxxxxxxx
-- -------------------------------------------------------------------------------------
-- The stored procedure for querying for all rows in table: vgProduct
-- -------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_vgProduct_Load]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_vgProduct_Load]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_vgProduct_Load
 AS
SELECT CCN,IdProduct
    ,lProductCode
    ,sProductName
    ,dtDateStartSubscription
    ,dtDateEndSubscription
    ,dtDateStartDownload
    ,dtDateEndDownload
    ,sDownloadFileName
    ,Application
    ,Version
    ,Beta
FROM vgProduct (nolock)
ORDER BY IdProduct
return @@error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC
ON sp_vgProduct_Load
TO dboxxxxxxxxx