c# - How Run A Stored Procedure (with Parameters - Has A return Value) From Code Behind? -

how can use stored procedure (with parameters - has return value of type int) code behind?

my stored procedure looks :

alter procedure [dbo].[sp_noskheh_sumoftotalpay]     @co_id int ----------------- declare @sum bigint ----------------- begin     select        @sum = sum(totalpay)     noskheh            (co_id = @co_id)      return @sum end 

i want use @sum in code behind ...

would please show me way doing ?

thanks in advance

best regards

you need set sqlconnection , sqlcommand. if have code return @sum statement in end, need (define parameter of type return_value):

using(sqlconnection _conn = new sqlconnection(-your-connection-string-here)) using(sqlcommand _cmd = new sqlcommand("dbo.sp_noskheh_sumoftotalpay", _conn)) {      _cmd.commandtype = commandtype.storedprocedure;     _cmd.parameters.add(new sqlparameter("@co_id", sqldbtype.int));    _cmd.parameters["@co_id"].value = 5; // whatever value want     _cmd.parameters.add(new sqlparameter("@return_value", sqldbtype.bigint));    _cmd.parameters["@return_value"].direction = parameterdirection.returnvalue;      _conn.open();    _cmd.executenonquery();     int64 result = int64.parse(_cmd.parameters["@return_value"].value);     _conn.close(); } 

it lot easier if replace return statement simple select:

select @sum 

in case, can use simplified version had before - using .executescalar() retrieve single value of single row being returned stored proc:

using(sqlconnection _conn = new sqlconnection(-your-connection-string-here)) using(sqlcommand _cmd = new sqlcommand("dbo.sp_noskheh_sumoftotalpay", _conn)) {      _cmd.commandtype = commandtype.storedprocedure;     _cmd.parameters.add(new sqlparameter("@co_id", sqldbtype.int));    _cmd.parameters["@co_id"].value = 5; // whatever value want     _conn.open();    object result = _cmd.executescalar();    _conn.close();     int64 sum = int64.parse(result); } 

that should call stored proc, read single value you're returning, , converting int variable called sum.