SQL Server variables not working

Question

I have a script with the following:

UPDATE table 
SET column to update = ? 
WHERE condition = ?", "text to insert", "text to test condition"

For some reason SQL is not executing or even reading this line. When I misspell any of the reserved words or column names I do not get an error.

HOWEVER, when I have

UPDATE table 
SET column to update = "text to insert" 
WHERE Name = "text to test condition"

SQL behaves as expected.

The problem is the second method,which works, is not adequate for my needs. Am I missing something?

Thanks

• Pawan Kumar: What error you are getting. Provide full code. Have u tried printing the code before executing the dynamic code.?
• Krismorte: Are you trying to pass the column name as variable?
• Squirrel: where are you executing this code ? is it in SQL Server Management Studio Query Window ?

Answer 1

Since this is tagged with pyodbc, I'm assuming you're trying to do run a query with parameters. Your code should probably read something like this:

pyodbc.execute(
 """
 UPDATE table 
 SET column_to_update = ? 
 WHERE other_column = ?
 """, 
 "text to put in column_to_update",
 "text to test condition in other_column",
)

Please note that parameters marked with a ? must be tied to a data typed object such as a column, so they can be bound. See:

https://github.com/mkleehammer/pyodbc/wiki/Getting-started#parameters

Good luck!

Answer 2

I'm going to assume that you are trying to run a SQL query from some client code by passing in variables with the query, though I'm not sure of this or what language you might be using - please clarify and add tags so we can help.

SQL Server does not use ? for parameter placeholders like in other DBMS queries (say, SQLite which uses ? in the way you are trying to use them). Instead, you need to either let an ORM tool declare variables for you, and use those, or explicitly declare your own parameters and pass them in. You haven't told us the development environment you're using or what method of SQL connection and ORM (if any) you're using, but here's a quick example using the very excellent Dapper ORM in C# from here, given an open connection conn:

string val = "my value";
conn.Execute("insert MyTable (val) values(@val)", new {val});
// or: conn.Execute("insert MyTable (val) values(@val)", new {val = val});"
conn.Execute("update MyTable set val = @val where Id = @id", new {val, id = 1});

In the background, Dapper handles the mapping and creation of variables, such that the actual SQL script received by SQL Server is something closer to this:

-- first query:
declare @val nvarchar(max) = 'my value';
insert MyTable (val) values(@val);
-- second query:
declare @val nvarchar(max) = 'my value', @id int = 1;
update MyTable set val = @val where Id = @id

On the other hand, if you are just going to execute a raw query directly with a SqlConnection, try something like this (equivalent to the first query above):

// Assumes an open connection conn
string val = "my value";
using (SqlCommand cmd = conn.CreateCommand())
{ 
 cmd.CommandText = "insert MyTable (val) values(@val)"; 
 cmd.Parameters.AddWithValue("@val", val); // This creates the @val declaration for you when the query is executed 
 cmd.ExecuteNonQuery();
}

Whatever you do, parameterize your parameters, and beware of SQL injection!

Hope that helps. If you'd like a clearer example, please give us some code to show how you're passing the query to the SQL Connection for execution.

• sigmatau: Thanks for the reply. To give more details I am running a SQL query from a python script. Here's how I connected: conn_str = ( r'DRIVER={SQL Server};' r'SERVER=localhost;' r'DATABASE=DBCompanyData;' r'Trusted_Connection=yes;' ) self.conn = pyodbc.connect(conn_str) self.cursor = self.conn.cursor and here is how I am posting data to db:
• sigmatau: ....and here's how i am posting data to the db: try: self.cursor.execute( "UPDATE EmployeeDetails SET Position = ? WHERE Name = ?", item['Employee_position'][0], item['Employee_name'][0] ) self.conn.commit() logging.info("\n\nUpdated Employee for -- %s\n", item['Employee_name']) return item except: print("\n-- Employee Details spider ERROR---")
• sigmatau: I must also add that I have used ? in a similar manner and it worked without a problem.
• sigmatau: SNAP....just went through the notes I sent and realized I put self.conn.cursor instead of self.conn.curser()....solved
• sigmatau: ...the only thing i need to understand is using ? directly in an SQL script.