CODEWITHSUNDEEP
c#asp.net
Developer
Developer

Reputation: 8636

How to return output parameter from sql in c#

Hi all I am having an output parameter defined in my stored procedure, my sp is as follows

createproc [dbo].[convertempID](@empid int,@totcnt varchar(4) output)
as
begin
    select @totcnt = right('00000'+convert(varchar(6), empID), 4)
        from tblEmployee
        where empID = @empid;
end

When I execute this I am getting result as 0015 for @totcnt but when I called this in my c# code I am getting 15 instead of 0015 can some one tell where I went wrong

cmd.CommandText = "convertempID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@empid", 15);
cmd.Parameters.Add(new SqlParameter("@totcnt", SqlDbType.Int));
cmd.Parameters["@totcnt"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
sring ans = cmd.Parameters["@totcnt"].Value.ToString();

Upvotes: 0

Views: 91

Answers (4)

Camilo Aguilar
Camilo Aguilar

Reputation: 129

Change the totcnt type from int to varchar

Upvotes: 0

Larry
Larry

Reputation: 18031

The return value type has to match the type :

try to replace

 cmd.Parameters.Add(new SqlParameter("@totcnt", SqlDbType.Int));

with 

 cmd.Parameters.Add(new SqlParameter("@totcnt", SqlDbType.VarChar));

Upvotes: 1

Habib
Habib

Reputation: 223187

You have specified parameter type as Int and there is no difference between 0015 and 15 for Int. Change your parameter line to:

cmd.Parameters.Add(new SqlParameter("@totcnt", SqlDbType.VarChar));

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499790

This is the problem:

cmd.Parameters.Add(new SqlParameter("@totcnt", SqlDbType.Int));

Compare that with the declaration in the stored procedure:

@totcnt varchar(4) output

Change the parameter type to SqlDbType.VarChar and it should work. At the moment the value will actually the integer 15 rather than the string "0015".

Upvotes: 4

Related Questions