locked
Re: SQL StoredProc Update RRS feed

  • Question

  • User1141310311 posted

    hi Folks

    Here is my problem, I have a storedProc that i need to update. It's only function is to DisAble a course. 

    Want I want now is for it to check if a certain course has a child course, It should disable it too.

    I only have one child course. with a Known CourseId. Please help build. See my tables below.

    CREATE PROCEDURE [dbo].[DisAbleUserCourse] (
    	@StudentId	BIGINT,
    	@CourseId BIGINT
    ) AS
    BEGIN
    	
    	 UPDATE [dbo].[CourseEnrolment]
    	 Set StatusId = 0
    	 WHERE StudentId = @StudentId
    	 AND	@CourseId = @CourseId
    	 
    END
    
    GO
    

    hi Folks
    Here is my problem, I have a storedProc that i need to update. It's only function is to DisAble a course.
    Wnat I want now is for it to check if a certain course has a child course, It should disable it too.
    I only have one child course. with a nown CourseId. Please help build. See my tables below.



    CREATE PROCEDURE [dbo].[DisAbleUserCourse] ( @StudentId BIGINT, @CourseId BIGINT) ASBEGIN UPDATE [dbo].[CourseEnrolment] Set StatusId = 0 WHERE StudentId = @StudentId AND @CourseId = @CourseId  END
    GO

    Thursday, October 20, 2016 8:45 AM

Answers

  • User-2057865890 posted

    Hi Willnsub,

    Firstly, find all child courses.

    Secondly, update status.

    DECLARE @Table TABLE(
        	CourseID INT,
        	CourseName VARCHAR(20),
        	ParentCourseName VARCHAR(20)
    )
    
    INSERT INTO @Table (CourseID,CourseName,ParentCourseName) SELECT 1, 'A', ''
    INSERT INTO @Table (CourseID,CourseName,ParentCourseName) SELECT 2, 'A-1', 'A'
    INSERT INTO @Table (CourseID,CourseName,ParentCourseName) SELECT 3, 'A-2', 'A-1'
    INSERT INTO @Table (CourseID,CourseName,ParentCourseName) SELECT 4, 'A-3', 'A-2'
    INSERT INTO @Table (CourseID,CourseName,ParentCourseName) SELECT 5, 'A-4', 'A-3'
    
    
    
    ;WITH ret AS(
        	SELECT	*
        	FROM	@Table
        	WHERE	CourseID = 3
        	UNION ALL
        	SELECT	t.*
        	FROM	@Table t INNER JOIN
        			ret r ON t.ParentCourseName = r.CourseName
    )
    
    SELECT  *
    FROM    ret

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 21, 2016 7:09 AM