I have a stored procedure in this format
CREATE PROCEDURE SP_MYTESTpROC
@VAR1 VARCHAR(10),
@VAR2 VARCHAR(20),
@BASEID INT ,
@NEWID INT OUTPUT
As Begin
INSERT INTO TABLE_NAME(username, firstname)
select @VAR1, @VAR2
WHERE ID = @BASEID
SET @NEWID = SCOPE_IDENTITY() AS INT
END
I am calling this stored procedure from C# code using dapper. My question is: how do I pass in the output parameter to the stored procedure while using dapper?
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?