c# - Stored procedure returning null values -

c# - Stored procedure returning null values -

i have stored procedure:

alter procedure usp_ins_usr_mst @usr_name varchar(max), @usr_mail varchar(max), @usr_code int output begin set nocount on; if not exists (select * usr_mst [usr_mail] = @usr_mail ) begin insert usr_mst([usr_name], usr_mail) values(@usr_name, @usr_mail); end set @usr_code = (select usr_code usr_mst usr_mail = @usr_mail) ; select @usr_code ; end

and c# code below:

using (sqlconnection cn = new sqlconnection(cm.sqlcnnstring)) { cn.open(); sqlcommand cmd = new sqlcommand("exec usp_ins_usr_mst @user, @email, @usr_code", cn); cmd.parameters.addwithvalue("@user", " "); cmd.parameters.addwithvalue("@email", httpcontext.current.session["uid"].tostring()); cmd.parameters.add("@usr_code", sqldbtype.int); cmd.parameters["@usr_code"].direction = parameterdirection.output; cmd.commandtimeout = 15000; cmd.executenonquery(); cn.close(); httpcontext.current.session["usr_code"] = cmd.parameters["@usr_code"].value.tostring(); }

but cmd.parameters["@usr_code"].value.tostring() returning empty string... can't figure out , missing.

try replace this

sqlcommand cmd = new sqlcommand("exec usp_ins_usr_mst @user,@email,@usr_code", cn);

to (add output clause exec)

sqlcommand cmd = new sqlcommand("exec usp_ins_usr_mst @user,@email,@usr_code output", cn); create proc #test_proc ( @param_in int, @param_out int output ) begin set @param_out = @param_in; end go declare @out1 int, @out2 int; exec #test_proc 1, @out1; -- returns null exec #test_proc 1, @out2 output; -- returns 1 select @out1; select @out2; drop proc #test_proc

c# sql-server