Insert Stored Procedure with null parameters

Course Queries Syllabus Queries 2 years ago

0 2 0 0 0 tuteeHUB earn credit +10 pts

5 Star Rating 1 Rating

Posted on 16 Aug 2022, this text provides information on Syllabus Queries related to Course Queries. Please note that while accuracy is prioritized, the data presented might not be entirely correct or up-to-date. This information is offered for general knowledge and informational purposes only, and should not be considered as a substitute for professional advice.

Take Quiz To Earn Credits!

Turn Your Knowledge into Earnings.

tuteehub_quiz

Answers (2)

Post Answer
profilepic.png
manpreet Tuteehub forum best answer Best Answer 2 years ago

 

I want to create an insert stored procedure with null parameters, if pass the value for that parameter then it has to insert or update in database

My stored procedure is:

Create proc [dbo].[SP_InsertOrUpdateCourseDetails]
    @CourseID int,
    @Tab1Title nvarchar(250) = null,
    @Tab1Description nvarchar(max) = null,
    @Tab2Title nvarchar(250) = null,
    @Tab2Description nvarchar(max) = null,
    @Tab3Title nvarchar(250) = null,
    @Tab3Description nvarchar(max) = null,
    @Tab4Title nvarchar(250) = null,
    @Tab4Description nvarchar(max) = null,
    @Syllabus nvarchar(max) = null
As
Begin

    If NOT EXISTS (Select * from CourseDetail Where CourseID=@CourseID )
    Begin
        Insert into CourseDetail(CourseID, Tab1Title, Tab1Description, 
                                 Tab2Title, Tab2Description,
                                 Tab3Title, Tab3Description, Tab4Title, Tab4Description,
                                 Syllabus)
        values (@CourseID, @Tab1Title, @Tab1Description, @Tab2Title, @Tab2Description,
                @Tab3Title, @Tab3Description, @Tab4Title, @Tab4Description, @Syllabus)

        IF @@ERROR = 0 AND @@ROWCOUNT =1
        Begin
            Select top 1 CourseID from CourseDetail Order by CourseDetailID Desc
        End
        Else 
        Begin
             Select 0
        End
    End
 Else
 Begin
     Update CourseDetail 
     SET
        Tab1Title = @Tab1Title,
        Tab1Description = @Tab1Description,
        Tab2Title = @Tab2Title,
        Tab2Description = @Tab2Description,
        Tab3Title = @Tab3Title,
        Tab3Description = @Tab3Description,
                                                
                                                
0 views
0 shares
profilepic.png
manpreet 2 years ago

You can use ISNULL function passing in it SP's parameter as first parameter and default value in case of insert or actual column's value in case of update:

    Insert into CourseDetail(CourseID, Tab1Title, Tab1Description, 
                             Tab2Title, Tab2Description,
                             Tab3Title, Tab3Description, Tab4Title, Tab4Description,
                             Syllabus)
    values (@CourseID, ISNULL(@Tab1Title, 'default value'), ISNULL(@Tab1Description,'default value'), ...


 Update CourseDetail 
 SET
    Tab1Title = ISNULL(@Tab1Title, Tab1Title),
    Tab1Description = ISNULL(@Tab1Description, Tab1Description),
    ....
 Where 
    CourseID = @CourseID

But in this case you won't can to set NULL value explicitely even if you'll need


0 views   0 shares

No matter what stage you're at in your education or career, TuteeHub will help you reach the next level that you're aiming for. Simply,Choose a subject/topic and get started in self-paced practice sessions to improve your knowledge and scores.