Thursday, January 27, 2011

output parameter in .net

SqlConnection con = GetConnection();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "countid";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = cmd.Parameters.AddWithValue("@mode", mode);
parameter = cmd.Parameters.AddWithValue("@logeduserid", logeduserid);
parameter = cmd.Parameters.AddWithValue("@updatedempid", updatedempid);
parameter = cmd.Parameters.Add("@result",SqlDbType.VarChar,10);
parameter.Direction = ParameterDirection.Output;
SqlDataReader dr = cmd.ExecuteReader();
if(!(mode=="V" || mode=="R")) id = parameter.Value.ToString();
DataTable dt = new DataTable();
dt.Load(dr); con.Close();
return dt;

/**************************************************
ALTER procedure [dbo].[countid]
(@mode varchar(2),@logeduserid varchar(10),@updatedempid varchar(10),@result nvarchar(20) output)
as begin
declare @a as bigint declare @b as bigint declare @c as bigint
select @a=isnull(max(employee_code),0)from HRMS_MAS_EMPLOYEE where len([employee_code])=5
set @c=len(@a)
set @result= substring('00000',1,5-@c)+cast(@a as varchar(6))

No comments:

Post a Comment