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