Insert Stored Procedure with null parameters

Course Queries Syllabus Queries 3 years ago

4.1K 2 0 0 0

User submissions are the sole responsibility of contributors, with TuteeHUB disclaiming liability for accuracy, copyrights, or consequences of use; content is for informational purposes only and not professional advice.

Answers (2)

Post Answer
profilepic.png
manpreet Tuteehub forum best answer Best Answer 3 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 3 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.

Similar Forum