Andrew Day Andrew Day - 11 months ago 53
SQL Question

How to use table variable with other parameter in SQL?

DECLARE @country varchar(30) = 'UK'
DECLARE @table varchar(50)
SET @table = 'NORTHWND.dbo.Employees'
EXEC('select e.LastName, e.FirstName, e.PostalCode
from ' + @table +' as e
where e.Country like '+ @country )


Gives:

Msg 207, Level 16, State 1, Line 33
Invalid column name 'UK'.

But I want to list all 'UK' Employees. Do I need to add parenthesis and how would I do that?

Answer Source

It looks like you're missing the ' ' around UK in your where clause, try this:

DECLARE @country varchar(30) = 'UK'
  DECLARE @table varchar(50)
  SET @table = 'NORTHWND.dbo.Employees'
  EXEC('select e.LastName, e.FirstName, e.PostalCode
  from ' + @table +' as e
  where e.Country like '''+ @country +'''' )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download