locked
Update Table with IN Clause Fails RRS feed

  • Question

  • User1769015664 posted

    In the code below, the nRetValue is returned as NULL and throws an error.

    If I run this Stored Procedure in SSMS, it fails with error 'Must declare the scalar variable "@DocStatus".' The same error for @AppID.

    if I change the procedure with hardcoded values for these two parameters then ONLY one record is updated when @DocNumber has multiple values in it.

           protected void DisableType(int ncount, string sVal)
            {
                int s = ncount;
                string s1 = sVal;
                bool bIsActive = false;
    
                int nRetVal = 0;
                string sType = txtDocType.Text;
                string nTypeCount = Session["TypeCount"].ToString();
    
                int sUserName = int.Parse(nTypeCount) + 1;
                String sLastName = sVal;
    
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                try
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand("Update_DocTypeStatus"))
                        {
                            using (SqlDataAdapter sda = new SqlDataAdapter())
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@AppID", 1);
                                cmd.Parameters.AddWithValue("@DocNumber", sVal);
                                cmd.Parameters.AddWithValue("@DocStatus", bIsActive);
                                cmd.Connection = con;
                                con.Open();
    
                                nRetVal = Convert.ToInt32(cmd.ExecuteScalar());
                                con.Close();
                            }
                        }
                        string message = string.Empty;
                        switch (nRetVal)
                        {
                            case -1:
                                lblTypeUpdate.Text = "Update failed...";
                                break;
                            default:
                                lblTypeUpdate.Text = "Updated successfully...";
                                LoadDocumentTypes();
                                break;
                        }
                    }
    
                }
                catch (SqlException e)
                {
                    string sMsg = e.InnerException.ToString();
                }
            }
    ALTER PROCEDURE [dbo].[Update_DocTypeStatus]
    	@DocStatus bit= NULL,
    	@AppID int= NULL,
    	@DocNumber varchar= NULL
    AS
    DECLARE @Returns BIT 
    DECLARE @RowCount INTEGER
    
    BEGIN
    	SET NOCOUNT ON;
    	DECLARE	@return_value int
    
    	BEGIN
    		exec('UPDATE DocumentType2 SET IsActive = @DocStatus WHERE AppID = @AppID AND DocNumber in ('+ @DocNumber +')')
    		if (@@ROWCOUNT = 0)
    			SET @Returns = 0
    		else
    			SET @Returns = 1
    	END
    	RETURN @Returns
    END

    Monday, November 30, 2020 2:28 AM

All replies

  • User753101303 posted

    Hi,

    ExecuteScalar is to return the first column of the first row returned by a SELECT statement.

    My personal preference is to just use https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=dotnet-plat-ext-5.0 which returns the number of affected rows without any extra work.

    If you really want to use a return value you have to read it back for example using:

    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter returnValue = new SqlParameter();
    returnValue.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(returnValue);
    cmd.ExecuteNonQuery(); // could return a row count ouf of the box
    // or then use (int)returnValue.Value to get the value returned by the SP

    Not directly related but it seems you could use directly a SqlCommand variable as the SqlDataAdapter seems to have no other purpose than to provide a SqlCommand property.
    Also showing exception messages to user is considered bad (could leak information, doesn't guarantee your app is checking if something is wrong, plus those actually in charge of keeping the app  up and running won't have those errors).

    Edit: and your SP returns 0 or 1 but you are testing for -1 on the C# side. IMO start with:

    nRetVal=cmd.ExecuteNonQuery();
    // and then later:
    switch
    (nRetVal) { case 0: lblTypeUpdate.Text = "Update failed..."; // Or "No row updated..." (is this really to be considered as a "failure"?) break; default: lblTypeUpdate.Text = "Updated successfully..."; // Could even tell how many rows were updated LoadDocumentTypes(); break; }

    Monday, November 30, 2020 12:18 PM
  • User452040443 posted

    NJ2, I believe that the best option would be to use a table-valued parameter, so you could execute this Update directly without this procedure:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

    If you want to keep the parameters the way you posted, try:

    exec(
    	'UPDATE DocumentType2 SET IsActive = ' + cast(@DocStatus as varchar) + 
    	' WHERE AppID = ' + cast(@AppID as varchar) +
    	' AND DocNumber in (' + @DocNumber + ')'
    );
    

    Hope this help

    Monday, November 30, 2020 1:37 PM
  • User753101303 posted

    Ah yes and for the IN it's weird that you still update one row (are you sure it was not from your earlier attempt?) rather than all selected rows. Double check the dynamic SQL string you are building.

    The benefit of using a table parameter is that you don't need any more to use dynamic SQL ie you could use something such as:
    var sql="UPDATE DocumentType2 SET IsActive=@DocStatus WHERE AppId=@AppId AND DocNumber IN (SELECT pk FROM @KeyList)";

    Depending on which SQL Server version you are using another option could be to use https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 :
    var sql="UDPATE DocumentType2 SET IsActive=@DocStatus WHERE AppId=@AppId AND DocNumber IN (SELECT value FROM STRING_SPLIT(@DocNumber,','))";

    Monday, November 30, 2020 4:21 PM