CODEWITHSUNDEEP
sqlsql-servert-sql
Ellbar
Ellbar

Reputation: 4054

TSQL table variable in WHERE statement causes error

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @user int;
DECLARE @aipNip varchar(20);
DECLARE @accessToBaseCompanies TABLE (baseCompanyId INT);

SET @user = 1;

INSERT INTO @accessToBaseCompanies (baseCompanyId) 
((SELECT c.BaseCompanyFk FROM [dbo].Companies c WHERE c.CompaniesTeamFk IN (SELECT u.CompaniesTeamFk FROM [dbo].[CompaniesTeams_Users] u WHERE UserFk = @user))
UNION ALL
(SELECT c.BaseCompanyFk FROM [dbo].Beneficiaries c WHERE c.DepartmentFk IN (SELECT u.DepartmentFk FROM [dbo].[Departments_Users] u WHERE UserFk = @user)))

SET @aipNip = (SELECT TOP 1 fc.[PureNip]
FROM [dbo].[BaseCompanies] bc
INNER JOIN [dbo].[Companies] c ON bc.Id = c.BaseCompanyFk AND c.CompanyType = 1
INNER JOIN [dbo].[Payment_Partners] fc ON fc.id = bc.CompanyPartnerFk)

SELECT bc.[Id]
      ,bc.[Name] as 'BaseCompany'
      ,SUM(cd.[PaidAmountNavireo]) - SUM(cd.[GrossTotal])
      ,SUM(CASE WHEN (ps.Id = 1 OR ps.Id = 3)
  FROM [dbo].[BaseCompanies] bc
  INNER JOIN [dbo].[Payment_CostDocuments] cd ON bc.Id = cd.BaseCompanyFk
  INNER JOIN [dbo].[PaymentStatuses] ps ON ps.Id = cd.PaymentStatusFk
  INNER JOIN [dbo].[Payment_Partners] fc ON fc.Id = cd.PartnerFk

  WHERE bc.[Id] IN @accessToBaseCompanies

Team BY
bc.[Id],
bc.[Name]

@accessToBaseCompanies is not empty, why when I try execute query I get error: Incorrect syntax near '@accessToBaseCompanies'.

Upvotes: 2

Views: 3881

Answers (3)

A. Belkin
A. Belkin

Reputation: 78

Recommend to use EXISTS instead IN, usually first a more productive(SQL Server IN vs. EXISTS Performance). Replace the

WHERE bc.[Id] IN @accessToBaseCompanies

by

WHERE EXISTS (SELECT 1 FROM @accessToBaseCompanies a where a.baseCompanyId = bc.[Id] )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The problem is this line:

WHERE bc.[Id] IN @accessToBaseCompanies

It should be:

WHERE bc.[Id] IN (select baseCompanyId from @accessToBaseCompanies)

Upvotes: 5

Andomar
Andomar

Reputation: 238068

The argument to IN is a value list or a subquery, not a table. Try:

WHERE bc.[Id] IN (select baseCompanyId from @accessToBaseCompanies)

Upvotes: 13

Related Questions