Insert Stored Procedure with null parameters

Question

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,
        Tab4Title = @Tab4Title,
        Tab4Description = @Tab4Description,
        Syllabus = @Syllabus
     Where 
        CourseID = @CourseID

     IF @@ERROR = 0 AND @@ROWCOUNT =1
     Begin
         Select top 1 CourseID from CourseDetail Order by CourseDetailID Desc
     End
     Else 
     Begin
         Select 0
     End
   End
 End

If i am not passing value from my C# code i dont want to insert or update null value — Avinash Singh
– Avinash Singh, Commented Sep 30, 2013 at 11:15
You should use the OUTPUT clause, to return the row you have just inserted/updated, rather getting the top 1 ordered by coursedetailID. Firstly your method won't work unless the update happens to update the latest course ID, and also has no issue with concurrency. — GarethD
– GarethD, Commented Sep 30, 2013 at 12:01
I've found in some combinations like JDBC and SQL server you have to put all the parameters that are potentially nullable at the end of the query for some reason — Matthew
– Matthew, Commented Jul 9, 2018 at 2:05

IUnknown · Accepted Answer · 2013-09-30 11:23:26Z

2

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

answered Sep 30, 2013 at 11:23

IUnknown

22.5k4 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Stored Procedure with null parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related