SQL Server - Table name as variable in query -

first, know has been addressed before - i've done research , you'll see below, have tried multiple versions work.

i trying set query in variable can set , query uses variable. here's code:

-- set category want check declare @validationcategory varchar = 'dept'  declare @validationtable varchar = (select validationtable masterfiles..categories category = @validationcategory , tabletovalidate = 'xref') declare @validationfield varchar = (select validationfield masterfiles..categories category = @validationcategory , tabletovalidate = 'xref')  exec(' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )' ) 

when run getting error:

msg 102, level 15, state 1, line 6
incorrect syntax near ')'.

the i've been able glean error level 15 means error correctable user. great! if find out syntax problem is...

i'm suspecting may due fact @validationtable table, not string, i'm not sure how address that.

after more research tried put query in variable , run exec on variable:

--... declare @query nvarchar set @query = ' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )' exec(@query) 

that ran without errors, didn't output - ran successfully.

after more research getting output of query executed variable, came (from example found here on so):

--... declare @vquery nvarchar set @vquery = ' select distinct category masterfiles.dbo.xref category = ''' + @validationcategory + '''     , (new_value not in (select ''' + @validationfield + '''                             ' + @validationtable + ')     , old_value not ''%new%''     or (new_value null or new_value = ''''))     )'  declare @vi int --declare @vquery varchar(1000)  exec sp_executesql     @query = @vquery     , @params = '@vi int output'     , @vi = @vi output  select @vi 

that resulted in error:

msg 214, level 16, state 3, procedure sp_executesql, line 1
procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

so what's correct way go this. in research on i've seen examples use stored procedure. i'd able without stored procedure because query used copy/pasting query query window in client's ssms, setting table variable , running query. moving on next client same thing.

i need able run on servers installed either sql server 2008 or 2012.

this first attempt generating..

select distinct category    masterfiles.dbo.xref   category = 'd'         , (new_value not in (select 'm' m)             , old_value not '%new%'             or (new_value null or new_value = '')         )     ) -- 

this has ) , doesn't make sense

on top of of this.. you're declaring variables varchar no length equals 1 character

declare @validationcategory varchar = 'dept'  declare @validationtable varchar = (select validationtable masterfiles..categories category = @validationcategory , tabletovalidate = 'xref') declare @validationfield varchar = (select validationfield masterfiles..categories category = @validationcategory , tabletovalidate = 'xref') 

@validationcategory equal 'd' in case.. make sure add appropriate lengths varchars