c# - Using Entity Framework, should I use RETURN or SELECT in my stored procedures? -

c# - Using Entity Framework, should I use RETURN or SELECT in my stored procedures? -

i trying utilize stored procedures entity framework , encountering problem homecoming type.

the homecoming type of stored procedure bit.

here code (simplified) of stored procedure :

create procedure [dbo].[sp_makestuff] @id int begin declare @status bit begin seek begin transaction delete mytable id = @id set @status = 1 commit end seek begin grab set @status = 0 rollback end grab homecoming @status end end

i added sp_makestuff in .edmx file homecoming type bool?.

then, seek execute code below

using (var ctx = new myentities()) { objectresult<bool?> result = ctx.sp_makestuff(id); if (result.firstordefault().value) { // stuff } }

however, raises exception of type system.data.entitycommandexecutionexception, message "the info reader returned store info provider not have plenty columns query requested."

after investigation, found works if replace

return @status

by

select @status

in past, used t-sql "return" keyword homecoming such result.

i wondering if new implementation correct. want utilize ef in projects, there mean create work "result" or have utilize "select" definitively ?

thanks.

edit 24/10/2014

based on responses below, re-wrote stored procedure. instead of returning something, raises error if goes wrong.

create procedure [dbo].[sp_makestuff] @id int begin begin seek begin transaction delete mytable id = @id commit end seek begin grab rollback raiserror('delete failed', 16, -1) end grab end end

then, told ef stored procedure returns nothing, , deal exception if raiserror called.

try { ctx.sp_web_deleteelement(elementid); // ... } grab (exception e) // message in e.innerexception.message { // ... }

in past, used t-sql "return" keyword homecoming such result.

a stored procedure homecoming code can integer. purpose indicate success or failure calling application, not homecoming data. homecoming info using output parameter or result set.

the homecoming value convention 0 indicates success , non-zero error/warning. non-zero value may used indicate specific specific cause (e.g. 99 client not found during update attempt) alternative raising error. default, sql server homecoming negative value if errors severity of 10 or higher raised.

c# sql-server entity-framework stored-procedures