xxxxxxxxxx
CREATE PROCEDURE uspFindProductByModel (
@model_year SMALLINT,
@product_count INT OUTPUT
) AS
BEGIN
SELECT
product_name,
list_price
FROM
production.products
WHERE
model_year = @model_year;
SELECT @product_count = @@ROWCOUNT;
END;
xxxxxxxxxx
Copy CodeCREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id)
FROM tblEmployee
WHERE Gender = @Gender
END
stored procedure with input and output parameters
xxxxxxxxxx
CREATE PROCEDURE spGetEmplloyeeNameById1
@ID INT,
@Name VARCHAR(30) OUTPUT
AS
BEGIN
SELECT @Name = Name FROM Employee WHERE ID = @ID
END
GO
-- For calling the procedure:
DECLARE @EmployeeName VARCHAR(30)
EXECUTE spGetEmplloyeeNameById1 3, @EmployeeName OUTPUT
PRINT @EmployeeName
--C#
string empName="";
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = _configuration.GetConnectionString("EmployeeAppCon");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetName";
cmd.Parameters.AddWithValue("@EmployeeID", id);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
cmd.Parameters["@Name"].Direction = ParameterDirection.Output;
try
{
conn.Open();
int i = cmd.ExecuteNonQuery();
//Storing the output parameters value in 3 different variables.
empName = Convert.ToString(cmd.Parameters["@Name"].Value);
// Here we get all three values from database in above three variables.
}
catch (Exception ex)
{
// throw the exception
}
finally
{
conn.Close();
}
return empName;