Can a value be assigned to an Output parameter and be used in a where clause?

I need to update a table using a stored procedure that "UPSERTS". In this case, @.OfficeId is passed with ParameterDirection.Output

So when I update I assign the value to OfficeId like this (the office id value is being correctly assigned):

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
if (_officeId > 0) { OfficeIdParam.Value = _officeId; }
OfficeIdParam.Direction = ParameterDirection.Output;

The following segment of the stored procedure is throwing an error that the OfficeId does not exist:

Update -- @.OfficeId int output

if not exists (select 1 from ORG_Office where OfficeId=@.OfficeId)
begin
RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.
return -1
end

May this approach work, and if the operation turns out to be an Insert I may do this:

select @.error_code = @.@.ERROR, @.OfficeId= scope_identity()

Thank you,

jspurlin

I am thinking you need to add the parameter conditionally, instead of adding it and then setting the direction conditionally. (you might need to fix syntax errors as I dont do .NET code and definetely not in C# )

If (_officeId > 0) {

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
OfficeIdParam.Direction = ParameterDirection.Output;

}

else {

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);

}