How to declare sql variable in C#

My requirement is to remove all record but one from a table. For this I am executing a series of two different sql commands. Seems like on ssms it works fine but with C# is not

--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<SqlParameter> param = new List<SqlParameter>()
    {
        new SqlParameter {ParameterName = "@State", SqlDbType = SqlDbType.VarChar, Value = state},
    };

    ExecQuery(queryToExec, param);
}

public static void ExecQuery(string query, List<SqlParameter> 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();
        }
    }
}

My qsns

How can I declare a sql variable correctly in C# (see ClearData method)
And, how to execute multiple queries in a single query string?(if I am doing this right)

EDIT

I came up with this to accomplish this. But still now luck. Please advise me to what to do:

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'

Answer

Maybe this can help you, change your query (queryToExec) with:

ALTER PROCEDURE uspWageDataByState
@State NVARCHAR(2)  
AS

DELETE TOP 
(CASE 
    (SELECT COUNT(*) FROM [Test] WHERE [STATE] = @State) 
    WHEN 0 THEN 1 
    ELSE (SELECT COUNT(*) FROM [Test] WHERE [STATE] = @State) END -1) 
FROM [Test]
WHERE [STATE] = @State;

if the declared variable is the problem you can troubleshoot with this, is not the best query, but whatever that you are using is not the best form neither :P.

I’m adding a 0 rows validation, in other way the sp crashed when no data found.

Advertisement

Answer