SQL Server - Table name as variable in query

Question

First, I know this has been addressed before - I've done my research and as you'll see below, I have tried multiple versions to get this to work.

I am trying to set up a query in which a variable can be set and the query uses that variable. Here's my code:

-- Set to the category you want to check
DECLARE @validationCategory varchar = 'Dept'

DECLARE @validationTable varchar = (SELECT ValidationTable FROM MasterFiles..Categories WHERE Category = @validationCategory AND TableToValidate = 'xref')
DECLARE @validationField varchar = (SELECT ValidationField FROM MasterFiles..Categories WHERE Category = @validationCategory AND TableToValidate = 'xref')

EXEC('
SELECT DISTINCT Category
FROM MasterFiles.dbo.xref
WHERE Category = ''' + @validationCategory + '''
    AND (new_value NOT IN (SELECT ''' + @validationField + '''
                            FROM ' + @validationTable + ')
    AND old_value NOT LIKE ''%New%''
    OR (new_value IS NULL OR new_value = ''''))
    )'
)

When I run it I am getting this error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

The most I've been able to glean from the error is that Level 15 means the error is correctable by the user. Great! Now if I could just find out exactly what the syntax problem is...

I'm suspecting it may be due to the fact that @validationTable is a table, not a string, but I'm not sure how to address that.

After some more research I then tried to put the query in a variable and then run EXEC on the variable:

--...
DECLARE @Query nvarchar
SET @Query = '
SELECT DISTINCT Category
FROM MasterFiles.dbo.xref
WHERE Category = ''' + @validationCategory + '''
    AND (new_value NOT IN (SELECT ''' + @validationField + '''
                            FROM ' + @validationTable + ')
    AND old_value NOT LIKE ''%New%''
    OR (new_value IS NULL OR new_value = ''''))
    )'
EXEC(@query)

That ran without errors, but I didn't get any output - just that it ran successfully.

After some more research about getting the output of a query as an EXECuted variable, I came up with this (from an example I found here on SO):

--...
DECLARE @vQuery nvarchar
SET @vQuery = '
SELECT DISTINCT Category
FROM MasterFiles.dbo.xref
WHERE Category = ''' + @validationCategory + '''
    AND (new_value NOT IN (SELECT ''' + @validationField + '''
                            FROM ' + @validationTable + ')
    AND old_value NOT LIKE ''%New%''
    OR (new_value IS 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 this error:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

So what's the correct way to go about this. In my research on this I've seen examples that use a stored procedure. I'd like to be able to do this without a stored procedure because this query will be used by copy/pasting the query into a query window in a client's SSMS, setting the table variable and running the query. Then moving on to the next client to do the same thing.

I need to be able to run this on servers installed with either SQL Server 2008 or 2012.

best way to figure out why your first attempt is wrong is to use PRINT to see what sql you're generating.. you have a ) in the wrong place somewhere — JamieD77
– JamieD77, Commented Jan 7, 2016 at 16:35

JamieD77 · Accepted Answer · 2016-01-07 16:44:53Z

This is what your first attempt is generating..

SELECT DISTINCT Category
FROM    MasterFiles.dbo.xref
WHERE   Category = 'D'
        AND (new_value NOT IN (SELECT 'M' FROM M)
            AND old_value NOT LIKE '%New%'
            OR (new_value IS NULL OR new_value = '')
        )
    ) -- extra

This has an extra ) and doesn't really make sense

On top of all of this.. you're declaring your variables as VARCHAR with no length which equals to 1 character

DECLARE @validationCategory varchar = 'Dept'

DECLARE @validationTable varchar = (SELECT ValidationTable FROM MasterFiles..Categories WHERE Category = @validationCategory AND TableToValidate = 'xref')
DECLARE @validationField varchar = (SELECT ValidationField FROM MasterFiles..Categories WHERE Category = @validationCategory AND TableToValidate = 'xref')

@validationCategory would equal to 'D' in this case.. Make sure you add appropriate lengths to your varchars

chancrovsky · Accepted Answer · 2016-01-07 16:38:27Z

0

First test, print the query variable ( @vQuery ), copy and paste it in a new query window and execute it. You're gonna identify where the error is exactly.

I think you need to do this, just like you do with your @validationTable:

DECLARE @vQuery nvarchar
SET @vQuery = '
SELECT DISTINCT Category
FROM MasterFiles.dbo.xref
WHERE Category = ''' + @validationCategory + '''
    AND (new_value NOT IN (SELECT ' + @validationField + '
                            FROM ' + @validationTable + ')
    AND old_value NOT LIKE ''%New%''
    OR (new_value IS NULL OR new_value = ''''))
    )'

answered Jan 7, 2016 at 16:38

chancrovsky

5803 silver badges11 bronze badges

Comments

David Rushton · Accepted Answer · 2016-01-07 16:52:10Z

0

If @validationTable is a table variable and not a string then you need to interact with it differently.

Instead of

(
    SELECT ''' + @validationField + '''
    FROM ' + @validationTable + '
)

Try

(
    SELECT ''' + QUOTENAME(@validationField) + '''
    FROM @validationTable
)

SQL Server will now recognise @validationTable as an object. I suspect, reading the error message, that your query has other issues. Try outputting all dynamic SQL statements and testing them directly when debugging.

I've added the QUOTENAME function, as this will help protect you against SQL Injection Attacks. It's a big topic but very much worth reading up on.

answered Jan 7, 2016 at 16:52

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Server - Table name as variable in query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related