Hello All

My Query is the procedure is as follows

select prs.id into xxemp
from cm_pers AS prs, fg_dom AS dom
where prs.split = dom.id and prs.username = UPPER('user1')
and dom.ci_name = LOWER('DOMAIN')
so my Question is what gets stored in xxemp if the select statement does not find any record with the conditions given above, i need to know this because after this i need to check a condition if xxemp contains a value then do this or else if it does not contain a value then do this. so what gets stored in xxemp if the record is not found in the table.

Thanks

Nothing!
Otherwise said, the host variable xxemp is not overwritten when the result table is empty.
The *correct* way to test for an empty result table is by checking that SQLCODE equals +100. But in this case you *may* also first set xxemp to (say) 0, then run the query, then verify whether xxemp is still 0. But you'll never know whether this is because xxemp was not changed, or whether you actually found a row with prs.id = 0.

Are there any guarantees that xxemp won't be changed by the DBMS? At least I wouldn't rely on that behavior - it could just result in the strangest problems.

Indeed -- a well-known situation is an SQLCODE -811 which you receive when more than one row satisfies your request, but you used a "SELECT .. INTO .."
In this case, typically, the *second* row's data will be returned.
But as Knut Stolze correctly mentioned, no guarantees here!
So, in any case, one *must* first check the SQLCODE and only use the returned data when SQLCODE=0.