USE [SDS_DevSchoolDistrict]
GO
/****** Object:  StoredProcedure [dbo].[AddAssessmentsToGroup]    Script Date: 02/07/2012 18:59:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddAssessmentTestGroup]
    @ApplicationUserId  int,
    --@AssessmentGroupId        int = NULL,
    @AssessmentTestGroupName    varchar(100) = NULL,
    @AssessmentTestIdList       varchar(4000)
AS
BEGIN

        /*---------------------------------------------------------
        -- Debugging
        ---------------------------------------------------------
        -- SELECT TOP 3 AssessmentId FROM Assessments ORDER BY AssessmentId
        -- SELECT * FROM AssessmentGRoups SG WHERE SG.ApplicationUserId IS NOT NULL
        DECLARE @ApplicationUserId int          SET @ApplicationUserId = 357
        DECLARE @AssessmentGroupId  int = 22588
        DECLARE @AssessmentIdList   varchar(4000) = '197, 198, 199'
        ---------------------------------------------------------*/


        DECLARE @AssessmentTestGroupId int
        ------------------------------------------------------
        INSERT  AssessmentTestGroups (AssessmentTestGroup, AssessmentTestGroupLabel, ApplicationUserId)
        VALUES (@AssessmentTestGroupName, @AssessmentTestGroupName, @ApplicationUserId)
        ------------------------------------------------------
        SET @AssessmentTestGroupId = SCOPE_IDENTITY()
        ------------------------------------------------------



        -------------------------------------------
        --
        -------------------------------------------
        DECLARE @AssessmentTestIds TABLE ( AssessmentTestId int )
        INSERT  @AssessmentTestIds
        SELECT  CAST(Value as int)
        FROM    dbo.SplitList(@AssessmentTestIdList, ',')
        -------------------------------------------
        --SELECT * FROM @AssessmentTestIds
        -------------------------------------------


        -------------------------------------------
        --
        -------------------------------------------
        INSERT  AssessmentTests_Groups(AssessmentTestId, AssessmentTestGroupId)
        SELECT  AssessmentTestId, @AssessmentTestGroupId
        FROM    @AssessmentTestIds S
        WHERE   NOT EXISTS (
                        SELECT  1
                        FROM    AssessmentTests_Groups S_G
                        WHERE   S_G.AssessmentTestId = S.AssessmentTestId
                          AND   S_G.AssessmentTestGroupId = @AssessmentTestGroupId
                )
        -------------------------------------------
        --SELECT * FROM Assessments_Groups WHERE AssessmentGroupId = @AssessmentGroupId
        -------------------------------------------



END
GO

USE [SDS_DevSchoolDistrict]
GO
/****** Object:  StoredProcedure [dbo].[AddAssessmentsToGroup]    Script Date: 02/07/2012 18:59:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteAssessmentTestGroup]
    @ApplicationUserId  int,
    @AssessmentTestGroupId      int = NULL
AS
BEGIN
        ------------------------------------------------------
        --
        ------------------------------------------------------
        IF(EXISTS(SELECT 1
                    FROM    AssessmentTestGroups ATG
                    WHERE   ATG.ApplicationUserId = @ApplicationUserId
                      AND   ATG.AssessmentTestGroupId = @AssessmentTestGroupId))
        BEGIN   ---------------------------------------------
                DELETE  AssessmentTests_Groups
                WHERE   AssessmentTestGroupId = @AssessmentTestGroupId
                ---------------------------------------------
                DELETE  AssessmentTestGroups
                WHERE   AssessmentTestGroupId = @AssessmentTestGroupId
        END     ---------------------------------------------
        -------------------------------------------



END
GO