CODEWITHSUNDEEP
c#sqldatabasesql-server-2008c#-4.0
HellKnight Hicks
HellKnight Hicks

Reputation: 125

Stored Procedure Delete query does not work. But when run in editor by itself and not called it works properly

I wrote this stored procedure using SQL Server Management Studio.

If I execute it in the editor and manually assign a formname and a user ID it works properly cleaning up the settings that don't match anything 

ALTER PROCEDURE [dbo].[Handover_DeleteBadParams] 
     @UserID varchar(10), 
     @formName varchar (30)
as
begin
   with deletethese as
   (     
       select 
           UserID, Param 
       from
           BI.dbo.Handover_RDL_SavedSettings userSettings
       left join
           BI.dbo.Handover_UI form on Form.FormName = userSettings.FormName 
                                   and userSettings.Param = Form.ItemValue
       where 
          userSettings.FormName = @UserID
          and userSettings.UserID = @formName
          and Form.FormName IS NULL)
   Delete from S
   from bi.dbo.Handover_RDL_SavedSettings S
   join deletethese D on S.UserId = D.UserID and S.Param = D.Param;
end

Then I call the stored procedure using C# 

using (SqlCommand kill = new SqlCommand("Handover_DeleteBadParams",cc))
{
    kill.CommandType = CommandType.StoredProcedure;
    kill.Parameters.Add("@UserID", SqlDbType.VarChar).Value = userID;
    kill.Parameters.Add("@formName", SqlDbType.VarChar).Value = working_form_name;

    kill.Connection.Open();
    kill.ExecuteNonQuery();
    kill.Connection.Close();
}

I get no errors or any sort of feedback during execution.... What am I doing wrong here?

Upvotes: 1

Views: 432

Answers (2)

Kyle Hale
Kyle Hale

Reputation: 8120

One problem I note is in your stored procedure you appear to have your parameters reversed:

 where userSettings.FormName = @UserID
 and userSettings.UserID = @formName

should probably be

 where userSettings.FormName =  @formName
 and userSettings.UserID = @UserID

Upvotes: 2

Reg Edit
Reg Edit

Reputation: 6916

Since it executes without any errors, but simply doesn't delete anything, we must conclude your userID and/or working_form_name don't contain the values you think they do.

Set a breakpoint in the C# just before the call, and check the values to verify that this is the problem.

Upvotes: 0

Related Questions