SQL temporary variable query Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery  AND typeId = (SELECT @id = typeId FROM EmpType, near @id =.  I'm looking at getting the typeid in a variable and use it in the next subquery following this one, not sure if that is not allowed.  Its SQL Server 2012.

Employee:

  empId empName
  1     Jim
  2     Sam


EmpType:

  typeId  type
  1         Full Time
  2         Contractor
  3         Temporary
  4         Work Permit


FamilyTree:

  familyId  familyName
  1         APAC
  2         Technology-APAC
  3         Domain-EU
  4         Platform-US


EmployeeMap:

  empId   typeId    familyId
  1         1       1
  1         2       2
  2         2       3
  2         3       3


Query:

  DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor'; 
  DECLARE @family VARCHAR (50) = 'Domain-EU'; 

  SELECT * FROM  EmployeeMap 
  WHERE empId = @empId
  AND typeId = (SELECT @id = typeId 
                FROM EmpType 
                WHERE type like @type 
                AND familyId = (SELECT familyId 
                                FROM FamilyTree 
                                WHERE familyName like @family))
You can rewrite this using JOINs rather than subselects to get what should be the correct answer.

This is assuming you are aiming to set the @id in this query and carry it forward to another query.

In this situation, the LIKE is only useful if you have a case sensitive collation. If you have case insensitive then you can just use =.

 DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor'; 
  DECLARE @family VARCHAR (50) = 'Domain-EU'; 

  SELECT TOP 1 @id = et.typeid
  FROM  EmployeeMap AS em
  INNER JOIN EmpType AS et ON et.typeId = em.typeId
  INNER JOIN FamilyTree AS ft ON ft.familyId = em.familyId
  WHERE empId = @empId
    AND type LIKE @type
    AND familyName LIKE @family;