1

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;

    }
Improve this question
2
  • Your question is unclear. Are you saying that if the @End value supplied in C# is not null then you want to update all rows where column End is NULL to @End, and similarly for all other parameters? The parameters are independent, i.e. @Start affects only rows where Start is NULL regardless of @End or End? Commented Jan 9, 2017 at 14:36
  • Not an answer to your question but an article you should read. sqlperformance.com/2012/10/t-sql-queries/sp_prefix Commented Jan 9, 2017 at 14:50

3 Answers 3

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

2 Comments

i want to for example : @start = 20170910 and other value is null . and other time is entered 2 value for update and other value is null.
c# answer added
0

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.
Improve this answer

3 Comments

end is date time 20171006 and type is char(8)
What are we assuming about variables like @AkharinBazdid, @Pid_Bazdid and @C_Bazdid, as these don't appear to be defined anywhere in the given sample procedure as variables or incoming parameters? Do we think this is just an incomplete snippet from the OP, or am I missing something?
Gordon Linoff , not work , no update because maybe i want to for example : @start = 20170910 and other value is null . and other time is entered 2 value for update and other value is null.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.