Tuesday, July 5, 2011

How to call stored procedure with OUTPUT parameter from code behind?


Here is the stored procedure in SQL Server

Create procedure GetDetails
@ID INT,
@Name Varchar(50) OUTPUT
AS
Begin
SELECT @Name = Emp_Name FROM EMPLOYEETABLE WHERE EMP_ID = @ID
END
Here @ID is normal parameter and @Name is output parameter 

To call this procedure in SQL Server

DECLARE @Name Varchar(50)
EXECUTE GETDETAILS 1, @Name OUTPUT
select @Name as Employee_Name


Code to call this procedure from C# is


SqlConnection con = new SqlConnection("Data Source=xxxx;Initial Catalog=Employee_Details;User ID=xx; Password=xxxxxx");
con.Open();
SqlCommand com = new SqlCommand("GetDetails",con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt16(textBox1.Text);
SqlParameter param = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param.Direction = ParameterDirection.Output;
com.Parameters.Add(param); 
           
com.ExecuteReader();
string a = com.Parameters["@Name"].Value.ToString();
con.Close();

No comments:

Post a Comment