Some people has asked me how to retrieve output parameters values from their stored procedures and since this example pretty much speaks for itself, here's the code:
CREATE PROCEDURE AddSomething( @zonename varchar(50), @desc varchar(255), @TheNewId int OUTPUT ) AS
BEGIN
INSERT INTO a_zone(zonename, descr) VALUES(@zonename, @desc)
SELECT @TheNewId=SCOPE_IDENTITY()
END
The C# code can then look like:
public int SaveAndGetNewId(string sName, string sDesc)
{
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = "...enter connstring...";
oConn.Open();
//Create command
SqlCommand Cmd = new SqlCommand("AddSomething", oConn);
Cmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParam = Cmd.Parameters.AddWithValue("@zonename", sName);
oParam = Cmd.Parameters.AddWithValue("@desc", sDesc);
oParam = Cmd.Parameters.AddWithValue("@TheNewId", 0);
oParam.Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
oConn.Close();
int nTheNewId = Convert.ToInt32(Cmd.Parameters["@TheNewId"].Value);
return nTheNewId;
}
