Passing Output parameters to stored procedure using dapper in c# code

Just searching the Test.cs file you could find this example

    public void TestProcSupport()
    {
        var p = new DynamicParameters();
        p.Add("a", 11);
        p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
        p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
        connection.Execute(@"create proc #TestProc 
                         @a int,
                             @b int output
                             as 
                             begin
                                 set @b = 999
                                 select 1111
                                 return @a
                             end");
        connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
        p.Get<int>("c").IsEqualTo(11);
        p.Get<int>("b").IsEqualTo(999);
    }

So, I suppose that your C# code could be written as

    public void InsertData()
    {
        var p = new DynamicParameters();
        p.Add("VAR1", "John");
        p.Add("VAR2", "McEnroe");
        p.Add("BASEID", 1);
        p.Add("NEWID", dbType: DbType.Int32, direction: ParameterDirection.Output);
        connection.Query<int>("SP_MYTESTpROC", p, commandType: CommandType.StoredProcedure);
        int newID =  p.Get<int>("NEWID");
    }

As a side note, do not use SP as prefix for your stored procedure. It is reserved for system defined procedures and you could find yourself in troubles if Microsoft decides to use the same name. Albeit improbable it is a bad practice and why risk?

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)