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

Question

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?

If you are trying to use null you need to add the parameter with a value of DBNull.Value. If the stored procedure expects a value for the @ID field then you are required to provide one. — Jacob Huckins
– Jacob Huckins, Commented Jun 22, 2020 at 21:15

Volodymyr Puzdriak · Accepted Answer · 2020-06-22 21:31:21Z

1

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.

answered Jun 22, 2020 at 21:31

Volodymyr Puzdriak

1375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

katrinawyh Over a year ago

I thought parameter direction only matters for stored procedure output parameter. did not know anything to return in the parameter needs to be specified as Output. Thank you!

Brent George · Accepted Answer · 2020-06-22 21:49:09Z

0

First things first, it doesn't appear your query will return a single int, but many result rows that each contain an OrderId. You are selecting OrderId from every row in the table and trying to assign it to a single int variable - this won't work. If you just want the greatest OrderId, you can try SELECT TOP 1 @ID = OrderId as seen below.

To answer the question, the other answers are correct that you need to specify the parameter direction as output. Output parameters are different than input parameters.

SELECT TOP (1)
    @id = OrderId
FROM 
    Orders
ORDER BY 
    OrderId DESC;

edited Jun 22, 2020 at 21:49

answered Jun 22, 2020 at 21:22

Brent George

1482 silver badges13 bronze badges

1 Comment

madreflection Over a year ago

You don't need a stored procedure to use output variables.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related