1

I am trying to write an SQL statement in python which passes a table name as a variable. However, I get the following error: Must declare the table variable "@P1". 

pypyodbc.Programming Error: ('42000', '[42000]' [Miscrosoft] [SQL SERVER NATIVE CLIENT 10.0] [SQL SERVER] Must declare the table variable "@P1"

The code yielding the ERROR is:

query = cursor.execute('''SELECT * FROM ?''', (table_variable,))

I have other code where I pass variables to the SQL statement using the same syntax which works fine (code below works as intended).

query = cursor.execute('''SELECT column_name FROM information_schema.columns WHERE table_name = ?''', (table_variable,))

The error seems to occur when I am using a variable to pass a table name.

Any help resolving this error would be much appreciated.

Improve this question
5
  • 1
    You can't use a parameter as the table name. To do this kind of thing requires dynamic sql. Commented Feb 5, 2016 at 14:30
  • Is this restriction limited to table name or are there other instances where parameters cannot be passed (I have not found any document detailing these restrictions). Commented Feb 5, 2016 at 14:34
  • 1
    Parameters are designed to be used in predicates to represent a value. That means you can use them in joins, where clauses etc. They are not designed to dynamically replace object names. If you want to use parameters as object name replacement you must use dynamic sql. And be careful if you go down this path you don't get a visit from bobby tables. bobby-tables.com Commented Feb 5, 2016 at 14:56
  • Appreciate the response, Sean. My intent with this code was to extract table names and their associated column names for each table in a db. I do not have prior knowledge of what's in the db, so this was my first attempt at researching the db contents and understanding what it contains. There are only 11 tables in this db, so writing separate code for each table to get column names and a sample of the data isn't difficult, but I can see how doing so would be burdensome when there are more tables. There may even be a better way to achieve what I was trying to do. Commented Feb 5, 2016 at 15:10
  • 1
    You could find the table and column names by querying the system tables. Commented Feb 5, 2016 at 15:35

3 Answers 3

Reset to default
3

With new comments from the OP this has changed rather significantly. If all you are trying to do is get a few rows of sample from each table you can easily leverage the sys.tables catalog view. This will create a select statement for every table in your database. If you have multiple schemas you could extend this to add the schema name too.

select 'select top 10 * from ' + QUOTENAME(t.name)
from sys.tables t
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

What you're trying to do is impossible. You can only pass values into queries as parameters - so 

SELECT * FROM @Table

is banned but

SELECT * FROM TableName WHERE Column=@Value

is perfectly legal.

Now, as to why it's banned. From a logical point of view the database layer can't cache a query plan for what you're trying to do at all - the parameter will completely and utterly change where it goes and what returns - and can't guarantee in advance what it can or can't do. It's like trying to load an abstract source file at runtime and execute it - messy, unpredictable, unreliable and a potential security hole.

From a reliability point of view, please don't do

SELECT * FROM Table

either. It makes your code less readable because you can't see what's coming back where, but also less reliable because it could change without warning and break your application.

I know it can seem a long way round at first, but honestly - writing individual SELECT statements which specify the fields they actually want to bring back is a better way to do it. It'll also make your application run faster :-)

Improve this answer

4 Comments

What the OP is trying to do is NOT impossible. You can easily leverage dynamic sql to do this. I would however recommend it is NOT a good idea to do that and i wholeheartedly agree that creating the individual queries is the preferred approach.
The OP's question assumes that SQL is parsed like a macro language and variable values dynamically inserted, which isn't true - hence why I said it was impossible in SQL. It would be possible with SAS or PowerShell, for example. The only way to do something analogous to the OP's attempted code in SQL would be dynamic SQL I agree, but that'd then be something like query = cursor.execute('''EXEC(''SELECT * FROM '' + ? + '')''', (table_variable,)) and a very long way from where we started, as well as a bad idea and an SQL injection attack waiting to happen.
Agreed with your concerns about dynamic sql but stating it isn't possible is not accurate. Yes we are a long way from what the OP started but that is because the way they started isn't going to work.
The feedback is sufficient in helping me understand that I will not be able to simply pass a table name as a parameter as I would a column name or similar value.
2

You can define a string variable:

table_var_str = 'Table_name'
st = 'SELECT * FROM ' + table_var_str
query = cursor.execute(st)

It will solve the problem.

You can also set the table_var_str as a list:

table_var_str = []
st = []
for i in range(N):
    table_var_str.append = 'Table_name' + str(i)
    st.append('SELECT * FROM ' + table_var_str[i])
for j in range(J):
    query = cursor.execute(st[j])

If the query is very long, you should write them in a line instead of multi lines.

Improve this answer

1 Comment

For anyone coming across this answer, this approach would leave you open to a SQL injection action so should be avoided.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.