CODEWITHSUNDEEP
sqlsql-servert-sqlnullsql-update
RedArmy
RedArmy

Reputation: 475

How to update Columns if value is not NULL

I want to update any columns of table TBL_Log if value entered from C# is not NULL. Here is my stored procedure:

Alter PROCEDURE [dbo].[SP_Update_User] 
(@User_id as int,@User_Names as nvarchar(max),@End as char(8),@Start as nvarchar(max) ,@Count as int)

AS
BEGIN


UPDATE [dbo].[TBL_Log]


     SET User_Names = @User_Names
      ,[Start] = @start
      ,[End] = @End
      ,[Count] = @Count
      where User_id = @User_id



END

I have tried to make this work but have not been successful.

code in class D1:

public static DataSet Update_User(int @User_id, string @User_Names, string @End, string @Start, int @Count)
    {

        SqlConnection myConnection = new SqlConnection(strConecctionString);


        SqlDataAdapter sqlcmd = new SqlDataAdapter("SP_Update_UserData_Bot", myConnection);
        sqlcmd.SelectCommand.CommandType = CommandType.StoredProcedure;


        SqlParameter parameterID_category_ID = new SqlParameter("@User_id", SqlDbType.Int);
        parameterID_category_ID.Value = User_id;
        sqlcmd.SelectCommand.Parameters.Add(parameterID_category_ID);

        SqlParameter parameterID_Part_ID = new SqlParameter("@User_Names", SqlDbType.Int);
        parameterID_Part_ID.Value = User_Names;
        sqlcmd.SelectCommand.Parameters.Add(parameterID_Part_ID);


        SqlParameter parameterID_Series_ID = new SqlParameter("@End", SqlDbType.Char);
        parameterID_Series_ID.Value = End;
        sqlcmd.SelectCommand.Parameters.Add(parameterID_Series_ID);

        SqlParameter parameterID_Model_ID = new SqlParameter("@start", SqlDbType.NVarChar);
        parameterID_Model_ID.Value = start;
        sqlcmd.SelectCommand.Parameters.Add(parameterID_Model_ID);

        SqlParameter parameterID_Count = new SqlParameter("@Count", SqlDbType.Int);
        parameterID_Count.Value = Count;
        sqlcmd.SelectCommand.Parameters.Add(parameterID_Count);


        sqlcmd.SelectCommand.CommandTimeout = int.MaxValue;
        DataSet DS = new DataSet();
        sqlcmd.Fill(DS);


        return DS;

    }

Upvotes: 1

Views: 3613

Answers (3)

Juan
Juan

Reputation: 3705

You should show your .Net code. Probably the best place to add the check is in .NET and don't call the stored procedure if the value you are worried about is NULL.

Also you must specify which value shouldn't be null, but assuming you meant any of them you can do:

IF (@User_Names IS NULL OR @start IS NULL OR @End IS NULL OR @Count IS NULL OR @User_Id IS NULL)
BEGIN
    RETURN
END

That will exit of the stored procedure if any of the parameters is null without updating the table

Given you c# code you can either don't call the stored procedure when a value is null or throw an exception. Also you should consider using DateTime instead of string for date values.

You can do the following in your c# code:

if (@User_Names == null || @End == null || @Start == null)
  return;

Or preferably

if (@User_Names == null || @End == null || @Start == null)
  throw new ArgumentNullException();

You can even check each parameter individually and pass its name as parameter to the Exception to give a meaningful error message.

Upvotes: 1

A.J. Schroeder
A.J. Schroeder

Reputation: 411

This will update only the values that are not null. If the value is null, the column is updated back to its own value.

UPDATE [dbo].[TBL_Log]
SET    User_Names = isnull(@User_Names, User_Names)
     , [Start] = isnull(@start, [Start])
     , [End] = isnull(@End, [End])
     , [Count] = isnull(@Count, [Count])
where User_id = @User_id

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Your commented out logic is fine:

UPDATE [dbo].[TBL_Log]
     SET User_Names = @User_Names,
         [Start] = @start,
         [End] = @End,
         [Count] = @Count
      where User_id = @User_id and
            (@User_Names is not null and
             @AkharinBazdid is not null and
             @Pid_Bazdid  IS NOT NULL and
             @C_Bazdid IS NOT NULL
            );

You can also use IF if you prefer.

Notes:

  • Don't use SQL keywords and reserved words as column names. The escape characters just get in the way.
  • If start and end are date/times, then use the appropriate types. Don't use strings.

Upvotes: 0

Related Questions