title: How to declare sql variable in C#
body: <p>My requirement is to remove all record but <strong>one</strong> from a table. For this I am executing a series of two different <code>sql</code> commands. Seems like on <code>ssms</code> it works fine but with C# is not</p>
  
  <pre><code>--This is what I run on ssms without any issue
  DECLARE @int INT;
  SELECT @int  =  COUNT(*) 
  FROM [Table] 
  WHERE STATE = 'CO';
  
  --Delete statement
  DELETE TOP (@int - 1 ) 
  FROM [Table] ;
  
  public static void ClearData(string state)
  {
      const string queryToExec = @"DECLARE @int INT;" +
                                 "SELECT @int  =  COUNT(*) " +
                                 "FROM [Table] " +
                                 "WHERE STATE = @State;" +
                                 "DELETE TOP (@int - 1 ) " + //NOTICE THIS LINE
                                 "FROM [Table] ";
  
      List&lt;SqlParameter&gt; param = new List&lt;SqlParameter&gt;()
      {
          new SqlParameter {ParameterName = "@State", SqlDbType = SqlDbType.VarChar, Value = state},
      };
  
      ExecQuery(queryToExec, param);
  }
  
  public static void ExecQuery(string query, List&lt;SqlParameter&gt; paramCollection = null)
  {
      using (SqlConnection conn = new SqlConnection(ConnString))
      {
          using (SqlCommand mySqlCom = new SqlCommand())
          {
              mySqlCom.CommandText = query;
              if (paramCollection != null) mySqlCom.Parameters.AddRange(paramCollection.ToArray());
              mySqlCom.Connection = conn;
              conn.Open();
              mySqlCom.ExecuteNonQuery();
          }
      }
  }
  </code></pre>
  
  <p>My qsns </p>
  
  <ol>
  <li>How can I declare a <code>sql</code> variable correctly in C# (see <em>ClearData</em> method)</li>
  <li>And, how to execute multiple queries in a single query string?(if I am doing this right)</li>
  </ol>
  
  <p><strong>EDIT</strong></p>
  
  <p>I came up with this to accomplish this. But still now luck. Please advise me to what to do:</p>
  
  <pre><code>IF OBJECT_ID ( 'uspWageDataByState', 'P' ) IS NOT NULL 
      DROP PROCEDURE uspWageDataByState;
  GO
  
  CREATE PROCEDURE uspWageDataByState
      @State NVARCHAR(2)  
  AS
  
      DECLARE @int INT
      SET @int  =  (SELECT COUNT(*) 
      FROM [Test] 
      WHERE [STATE] = @State)
  
      DELETE TOP (@int - 1 ) 
      FROM [Test]
      WHERE [STATE] = @State;
  
  GO
  
  exec uspWageDataByState 'CO'
  </code></pre>