How to return output parameter from sql in c#

Question

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();

I'm not sure, but I guess it might be because you have selected SqlDbType.Int. If this is parsed as an int, it will be a number, and an int cannot have leading 0's. — Øyvind Bråthen
– Øyvind Bråthen, Commented Feb 22, 2013 at 12:51

Jon Skeet · Accepted Answer · 2013-02-22 12:51:20Z

4

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".

answered Feb 22, 2013 at 12:51

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Larry Over a year ago

No chars enough to write "I fold".

Habib · Accepted Answer · 2013-02-22 12:51:44Z

1

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));

answered Feb 22, 2013 at 12:51

Habib

224k30 gold badges420 silver badges446 bronze badges

Comments

Larry · Accepted Answer · 2013-02-22 12:53:06Z

1

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));

answered Feb 22, 2013 at 12:53

Larry

18.1k9 gold badges83 silver badges108 bronze badges

Comments

Camilo Aguilar · Accepted Answer · 2013-02-22 12:56:45Z

0

Change the totcnt type from int to varchar

answered Feb 22, 2013 at 12:56

Camilo Aguilar

1291 silver badge11 bronze badges

Collectives™ on Stack Overflow

How to return output parameter from sql in c#

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related