Stored procedure returning null values

Question

I have a stored procedure:

ALTER PROCEDURE usp_ins_usr_mst        
    @USR_NAME VARCHAR(max),        
    @USR_MAIL VARCHAR(max),    
    @USR_CODE INT OUTPUT        
AS        
BEGIN        
    SET NOCOUNT ON;  

    IF NOT EXISTS (SELECT * FROM USR_MST WHERE [USR_mail] = @USR_MAIL )        
    BEGIN        
       INSERT INTO USR_MST([USR_NAME], USR_MAIL) 
       VALUES(@USR_NAME, @USR_MAIL);        
    END        

    SET @USR_CODE = (SELECT USR_CODE FROM USR_MST WHERE USR_MAIL = @USR_MAIL)  ;  
    select @USR_CODE ;  
END

and the C# code as 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 this cmd.Parameters["@USR_CODE"].Value.ToString() is returning empty string... I can't figure out where and what I am missing.

Empty string is not the same as null value. But maybe this would help http://stackoverflow.com/questions/3309213/getting-return-value-from-stored-procedure-in-ado-net — Sami Kuhmonen, Nov 02 '14 at 19:02

Andrey Morozov · Accepted Answer · 2014-11-02T19:15:56.197

0

Try to replace this

SqlCommand cmd = new SqlCommand("exec usp_ins_usr_mst @user,@email,@USR_CODE", cn);

to this (add OUTPUT clause to 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
) as
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

edited Nov 02 '14 at 19:15

answered Nov 02 '14 at 19:08

Andrey Morozov

7,839
5
53
75

@Rahul I think `Direction` make sense only if `CommandType = CommandType.StoredProcedure` but in our case exec expression is used and therefore it must satisfy the stored procedure execution rules... – Andrey Morozov Nov 02 '14 at 19:42

score 0 · Answer 2 · answered Nov 02 '14 at 19:17

First of all change your procedure to use SCOPE_IDENTITY() instead like

ALTER PROCEDURE usp_ins_usr_mst        
@USR_NAME VARCHAR(max),        
@USR_MAIL VARCHAR(max),    
@USR_CODE INT OUTPUT        
AS        
BEGIN        
  SET NOCOUNT ON;  
 IF NOT EXISTS (SELECT * FROM USR_MST WHERE [USR_mail] = @USR_MAIL )        
 BEGIN        
  INSERT INTO USR_MST([USR_NAME],USR_MAIL) VALUES(@USR_NAME,@USR_MAIL);        
 END  

 SELECT @USR_CODE = SCOPE_IDENTITY();   
END

Try keeping the parameter names same as in procedure. One mistake you did; you are trying to get the OUTPUT parameter value after closing connection (as pointed below); whereas you should get it before closing connection.

cn.Close();

Session["USR_CODE"] = cmd.Parameters["@USR_CODE"].Value.ToString();

Modify your code like below

        SqlCommand cmd = new SqlCommand("dbo.usp_ins_usr_mst", cn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@USR_NAME", " ");
        cmd.Parameters.AddWithValue("@USR_MAIL", Session["uid"].ToString());

        cmd.Parameters.Add("@USR_CODE", SqlDbType.Int);
        cmd.Parameters["@USR_CODE"].Direction = ParameterDirection.Output;
        cmd.CommandTimeout = 15000;
        cmd.ExecuteNonQuery();

        Session["USR_CODE"] = cmd.Parameters["@USR_CODE"].Value.ToString();

        cn.Close();

Stored procedure returning null values

2 Answers2