Wednesday 1 July 2009

SQL output parameters in stored procedures

In C# add parameter of type output and obtain its value after cmd execution:


   1:  cmd.Parameters.Add("@newId", SqlDbType.Int);
   2:  cmd.Parameters["@newId"].Direction = ParameterDirection.Output;
   3:  cmd.ExecuteNonQuery();
   4:  int result = (int)cmd.Parameters["@newId"].Value;
   5:  return result;

In sproc, declare the parameter as type output:


   1:  Create procedure... (
   2:  @param1 int, etc
   3:  @newId int OUTPUT
   4:  )
   5:  begin
   6:  ...
   7:  set @newId = 123
   8:  return
   9:  end

No comments: