Wednesday, 1 July 2009

SQL output parameters in stored procedures

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

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

In sproc, declare the parameter as type output:

Create procedure... (
@param1 int, etc
@newId int OUTPUT
)
begin
...
set @newId = 123
return
end

0 comments: