I'm trying execute a stored procedure using Dapper. I'm getting an error:

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@Referans".'

Here is my code:

var dosyaDetaySql = @"exec TurkmenBeyannameDetaylar @Referans";
var dosyaDetay = conn.Query<TurkmenBeyannemeSabitleriDetay>(dosyaDetaySql, new { dosyaResult.REF }).ToList();

I tried @"exec TurkmenBeyannameDetaylar '@Referans'"; but this isn't working either. How can I change it so that the stored procedure executes successfully?

CodePudding user response：

You are passing the query syntax wrong. It should be like:

var dosyaDetaySql = "exec TurkmenBeyannameDetaylar @Referans";
var dosyaDetay = conn
     .Query<TurkmenBeyannemeSabitleriDetay>(dosyaDetaySql, new { Referans = dosyaResult.REF})
     .ToList();

BTW, you might have problems with DynamicParameters declared this way. Instead it is easier IMHO to write as:

var parameters = new DynamicParameters();
parameters.Add("@Referans", dosyaResult.REF);

var dosyaDetaySql = @"exec TurkmenBeyannameDetaylar @Referans";
var dosyaDetay = conn
    .Query<TurkmenBeyannemeSabitleriDetay>(dosyaDetaySql, parameters)
    .ToList();

PS: It returns that error when the parameter name is wrong too. Be sure that SP parameter name (@Referans) matches in code where parameters are added.

EDIT: The samples below were added just to explain why I added that DynamicParameters. You wouldn't need it but in some cases you might find it easier. Decide yourself.

void Main()
{
    var query = @"EXEC dbo.CustOrdersDetail @ID";
    var parameters = new {ID=10280};
    var result = GetResults<dynamic>(query, parameters);
    foreach (var order in result)
    {
        Console.WriteLine($"{order.ProductName}, {order.UnitPrice}, {order.Discount}, {order.ExtendedPrice}");
    }

    Console.WriteLine("=====================================");
    var parameters2 = new DynamicParameters();
    //parameters2.AddDynamicParams(new {ID=10280}); // not easy for everyone to write - I don't know why I think that way
    parameters2.Add("@ID", 10280, DbType.Int32);

    var result2 = GetResults_2<dynamic>(query, parameters2);
    foreach (var order in result2)
    {
        Console.WriteLine($"{order.ProductName}, {order.UnitPrice}, {order.Discount}, {order.ExtendedPrice}");
    }
}

private static IEnumerable<T> GetResults<T>(string query, object parameters = null)
{
    using (IDbConnection db = new SqlConnection(@"Server=.\SQLExpress2012;Database=Northwind;Trusted_Connection=true"))
    {
        return db.Query<T>(query, parameters);
    }
}

public static IEnumerable<T> GetResults_2<T>(string query, DynamicParameters parameters=null)
{
    using (IDbConnection db = new SqlConnection(@"Server=.\SQLExpress2012;Database=Northwind;Trusted_Connection=true"))
    {
        return db.Query<T>(query, parameters);
    }
}

Both have the same result:

Guaraná Fantástica, 3.6000, 0, 43.2000
Pâté chinois, 19.2000, 0, 384.0000
Rhönbräu Klosterbier, 6.2000, 0, 186.0000
=====================================
Guaraná Fantástica, 3.6000, 0, 43.2000
Pâté chinois, 19.2000, 0, 384.0000
Rhönbräu Klosterbier, 6.2000, 0, 186.0000