A stored proc should return 0 for success, and any other value for a failure.
Here is an example table, filled with data:
CREATE TABLE DemoData ( id INT NOT NULL PRIMARY key, someValue DECIMAL(4,4) NOT NULL ) GO INSERT INTO DemoData(id, someValue) VALUES (1, 1.23), (2, 2.34), (3, 3.45), (4, 4.56)
Here are our example stored procedures:
CREATE PROCEDURE GetDemoData(@maxId INT) AS BEGIN SET NOCOUNT ON; SELECT id, someValue FROM DemoData WHERE id <= @maxId END GO CREATE PROCEDURE AddTwoValues(@a INT, @b INT) AS BEGIN SET NOCOUNT ON; RETURN @a + @b -- Don't do this. Stored procs should return -- 0 for success, and any other value for failure. END GO CREATE PROCEDURE AddTwoValuesWithResult(@a INT, @b INT, @result INT OUTPUT, @result2 INT OUTPUT) AS BEGIN SET NOCOUNT ON; SET @result = @a + @b SET @result2 = @b - @a END GO CREATE PROCEDURE ConvertToString(@someValue INT, @someString VARCHAR(20) OUTPUT) AS BEGIN SET NOCOUNT ON; SET @someString = '*' + CAST(@someValue AS VARCHAR(20)) + '*' END GO
Here is the C# code to call the above:
public class DemoData { public Int32 Id { get; set; } public Decimal? SomeValue { get; set; } } public class MyDbContext : DbContext { static MyDbContext() { Database.SetInitializer<MyDbContext>(null); } public MyDbContext() : base("Name=MyDbContext") { } public MyDbContext(string connectionString) : base(connectionString) { } public MyDbContext(string connectionString, System.Data.Entity.Infrastructure.DbCompiledModel model) : base(connectionString, model) { } // Stored Procedures public int AddTwoValues(int a, int b) { var procResult = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; Database.ExecuteSqlCommand( "exec @procResult = AddTwoValues @a, @b", new object[] { new SqlParameter { ParameterName = "@a", Value = a, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, new SqlParameter { ParameterName = "@b", Value = b, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, procResult }); return (int)procResult.Value; } public int AddTwoValuesWithResult(int a, int b, out int result, out int result2) { var resultParam = new SqlParameter { ParameterName = "@result", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; var result2Param = new SqlParameter { ParameterName = "@result2", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; var procResult = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; Database.ExecuteSqlCommand( "exec @procResult = AddTwoValuesWithResult @a, @b, @result OUTPUT, @result2 OUTPUT", new object[] { new SqlParameter { ParameterName = "@a", Value = a, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, new SqlParameter { ParameterName = "@b", Value = b, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, resultParam, result2Param, procResult }); result = (int)resultParam.Value; result2 = (int)result2Param.Value; return (int)procResult.Value; } public int ConvertToString(int someValue, out string someString) { var someStringParam = new SqlParameter { ParameterName = "@someString", SqlDbType = SqlDbType.VarChar, Size = 20, Direction = ParameterDirection.Output }; var procResult = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; Database.ExecuteSqlCommand( "exec @procResult = ConvertToString @someValue, @someString OUTPUT", new object[] { new SqlParameter { ParameterName = "@someValue", Value = someValue, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, someStringParam, procResult }); someString = (string)someStringParam.Value; return (int)procResult.Value; } public List<DemoData> GetDemoData(int maxId, out int procResult) { var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output }; var sqlQuery = Database.SqlQuery<DemoData>( "exec @procResult = GetDemoData @maxId", new object[] { new SqlParameter { ParameterName = "@maxId", Value = maxId, SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input }, procResultParam }).ToList(); procResult = (int) procResultParam.Value; return sqlQuery; } }
To call the above and return the results:
using (var db = new MyDbContext()) { Console.WriteLine("AddTwoValuesWithResult"); int result, result2; int procResult = db.AddTwoValuesWithResult(3, 7, out result, out result2); Console.WriteLine("result = " + result); Console.WriteLine("result2 = " + result2); Console.WriteLine("procResult = " + procResult); Console.WriteLine(""); Console.WriteLine("ConvertToString"); string someString; procResult = db.ConvertToString(56, out someString); Console.WriteLine("someString = " + someString); Console.WriteLine("procResult = " + procResult); Console.WriteLine(""); Console.WriteLine("AddTwoValues"); procResult = db.AddTwoValues(5, 10); Console.WriteLine("procResult = " + procResult); Console.WriteLine(""); Console.WriteLine("GetDemoData"); var list = db.GetDemoData(3, out procResult); Console.WriteLine("procResult = " + procResult); foreach (var i in list) { Console.WriteLine(i.Id + ", " + i.SomeValue); } }
Yields the following output:
AddTwoValuesWithResult result = 10 result2 = 4 procResult = 0 ConvertToString someString = *56* procResult = 0 AddTwoValues procResult = 15 GetDemoData procResult = 0 1 1.230 2 2.340 3 3.450
This will be available in the EntityFramework Reverse POCO Generator, available at https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838