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

What error you are getting. Provide full code. Have u tried printing the code before executing the dynamic code.? — Pawan Kumar, Feb 02 '18 at 02:21
where are you executing this code ? is it in `SQL Server Management Studio` `Query Window` ? — Squirrel, Feb 02 '18 at 04:20

score 1 · Answer 1 · answered Feb 03 '18 at 22:25

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!

score 0 · Answer 2 · edited Feb 02 '18 at 05:32

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.

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, Feb 02 '18 at 21:08
....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, Feb 02 '18 at 21:11
I must also add that I have used ? in a similar manner and it worked without a problem. — sigmatau, Feb 02 '18 at 21:13
SNAP....just went through the notes I sent and realized I put self.conn.cursor instead of self.conn.curser()....solved — sigmatau, Feb 02 '18 at 21:37
...the only thing i need to understand is using ? directly in an SQL script. — sigmatau, Feb 02 '18 at 21:39
Aha! Then my answer is irrelevant. I suggest you add those details to your question and tag it with python, pyodbc (if that's the library you're using, and anything else that might help attract the right assistance. I'll keep this answer here for reference but can't help you unfortunately. — pcdev, Feb 02 '18 at 21:50

SQL Server variables not working

2 Answers2