CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
daOnlyBG
daOnlyBG

Reputation: 601

Dynamically querying a table with a variable name yields a vague "Incorrect syntax near 'Select'" Error

I'm trying to query the top 5 rows in an existing table with a dynamically generated name. I understand that many on SO have had problems/questions doing something similar, and I've read through a handful of solutions, but all to no avail.

I can generate the table name just fine; the issue is then taking the table name and using it in another script, namely something like SELECT TOP 5 * FROM (table name here).

Here is what I have now: 

DECLARE @oldtablename VARCHAR(MAX)
DECLARE @oldtablequery VARCHAR(MAX)

SET @oldtablename = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE ''%tablekeywords_test%'' 
AND CONVERT(varchar(8), RIGHT(TABLE_NAME, 8), 112) <= CONVERT(VARCHAR(8), GETDATE(), 112)'
exec(@oldtablename)

-- up until here, everything works fine. The following does not:
--EXEC('select top 5 * from databasename.dbo.'+@oldtablename+'')
SET @oldtablequery = 'SELECT TOP 5 * FROM databasename.dbo.'+@oldtablename+';'
exec(@oldtablequery)

The error I get with both the exec( ) attempt (the one in comments) and the Set...exec attempt is: 

(1 row(s) affected)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Unfortunately, I have no idea which 'SELECT' error the compiler refers to. I'm going to guess this isn't a syntax error, but perhaps a logical one (i.e., there's something fundamental I am or am not doing rather than omitting a comma or something), but I'm not entirely certain, as I am new to dynamic SQL.

Thank you in advance.

EDIT FOR CLARIFICATION: I'm not struggling to generate or print the desired table name. I am trying to use the table name in a new query to select the 5 top rows of that specific table, @oldtablename.

Upvotes: 0

Views: 122

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

Eli's answer points out the problem. There was no need to use dynamic SQL in the first query. This would solve the problem:

DECLARE @oldtablename VARCHAR(MAX)
DECLARE @oldtablequery VARCHAR(MAX)

SET @oldtablename = (
     SELECT 
         TOP 1 TABLE_NAME 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_NAME LIKE '%tablekeywords_test%' 
     AND CONVERT(varchar(8), RIGHT(TABLE_NAME, 8), 112) <= CONVERT(VARCHAR(8), GETDATE(), 112));


-- up until here, everything works fine. The following does not:
--EXEC('select top 5 * from databasename.dbo.'+@oldtablename+'')
SET @oldtablequery = 'SELECT TOP 5 * FROM databasename.dbo.'+@oldtablename+';'
exec(@oldtablequery)

Upvotes: 2

Eli
Eli

Reputation: 2608

Your first query is returning a query, i.e. a select statement.
Your second query is trying to select the top 5...from the select statement... which doesn't make sense.

Please look at the following - it is your query, with the change of printing out the query instead of executing it. Look at that query which it prints out, and the issue should be self-explanatory. 

DECLARE @oldtablename VARCHAR(MAX)
DECLARE @oldtablequery VARCHAR(MAX)

SET @oldtablename = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE ''%tablekeywords_test%'' 
AND CONVERT(varchar(8), RIGHT(TABLE_NAME, 8), 112) <= CONVERT(VARCHAR(8), GETDATE(), 112)'
exec(@oldtablename)

-- up until here, everything works fine. The following does not:
--EXEC('select top 5 * from databasename.dbo.'+@oldtablename+'')
SET @oldtablequery = 'SELECT TOP 5 * FROM databasename.dbo.'+@oldtablename+';'
print @oldtablequery

If you would like to preserve just the table name from the first query, you'll need to use a second variable to capture that.

Upvotes: 3

Related Questions