How to fix an error in MSSQL query?

Question

SQL

Moving from Oracle to MSSQL, rewriting functions and procedures. Here is the part of the request that gives the error:

select @g_count = count(*) from Award where awrd_personid = @personid
and awrd_deleted is null
and awrd_notinuse is null
and (awrd_awardtempid, awrd_discipline) in 
(select awrs_reqawrdtempid, awrs_discipline from awardrestriction 
where (awrs_competitionid = @competitionid or awrs_eventid = @eventid)
and awrs_persontype = 'Judge' and awrs_deleted is null);

As I understand it, the error crashes right here:
..and (awrd_awardtempid, awrd_discipline) in..
How to fix it?
Thanks

Answer 1

... and exists
(
select 1
from awardrestriction 
where 
(awrs_competitionid = @competitionid or awrs_eventid = @eventid)
and awrs_persontype = 'Judge' 
and awrs_deleted is null

and awrd_awardtempid = Award.awrd_awardtempid
and awrd_discipline = Award.awrd_discipline
)

Answer 2

I would replace the in construction with a direct join (INNER JOIN), that's all.
like this:

select @g_count = count(*) 
from Award 

/*Конструкцию in перенес вот сюда*/
join (
  select awrs_reqawrdtempid, awrs_discipline 
  from awardrestriction 
  where (awrs_competitionid = @competitionid or awrs_eventid = @eventid)
  and awrs_persontype = 'Judge' and awrs_deleted is null
) r on Award.awrd_awardtempid=r.awrs_reqawrdtempid 
  and Award.awrd_discipline=r.awrs_discipline

where awrd_personid = @personid
and awrd_deleted is null
and awrd_notinuse is null
--and (awrd_awardtempid, awrd_discipline) in