Search code examples
c#sqlcommandsqlclientsqlparameter

Create SqlParameter with name and type only (no value assigned)

I am having issue to use SqlClient to call some sql query with parameter declared but not assigned. The query I want to run is similar to below

DECLARE @ID int;
SELECT @ID = OrderId
FROM Orders
ORDER BY OrderId DESC;
SELECT @ID;

However, when I created SqlParameter as following

SqlParameter parameter = new SqlParameter("ID", SqlDbType.Int);
sqlcommand.Parameters.Add(parameter);

I got error showing:

"The parameterized query
'(@ID int)SELECT @ID = OrderId
FROM Orders
ORDER BY OrderId DES'
expects the parameter '@ID', which was not supplied."

Any suggestions on how to create SqlParameter without any value assigned in such case?

Solution

  • In your case you have to specify that @ID is an output parameter.

    SqlParameter parameter = new SqlParameter("ID", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
}

    When the query will be executed you can retrieve that value via

    parameter.Value

    It will return an instance of the object type.