I've been under the impression that quoting integers in SQL queries is frowned upon, but I've recently learned that prominent frameworks like Django adheres to this practice. Is it in fact perfectly acceptable?
+7
A:
The question implies that you put naked values in your SQL query in the first place. I think the most "acceptable" practice would be to parameterize the query instead - that way you don't have to concern yourself with issues such as this; let the data access library handle it for you instead.
Aaronaught
2010-02-03 17:44:45
+1 for parameterisation!
AdaTheDev
2010-02-03 17:46:24
+1, not using bind-params is a far worse idea in a framework.
macabail
2010-02-03 17:48:12
While parameterisation sounds like a good idea, it's not really worth the gazillion lines of boilerplate code it requires. You'll find many successful apps without it (like Django, I guess)
Andomar
2010-02-03 17:50:13
@Andomar: It's simple enough to write a utility method that will do the parameterization for you. I'm not a Django expert, but in Linq to SQL for example, you can write an ad-hoc query as `ExecuteQuery("SELECT Name FROM Customers WHERE ID = {0}", customerID)`. No boilerplate whatsoever! And if this type of method/function isn't readily available, you can put one together in a few minutes.
Aaronaught
2010-02-03 17:54:35
@Aaronaught: Linq's `ExecuteQuery` takes parameters in the same way as `String.Format()`. That's not what is meant by SQL parameterisation! The later goes like (prepares yawn): `SqlParameter param = new SqlParameter(); param.ParameterName = "@City"; param.Value = inputCity; cmd.Parameters.Add(param);`
Andomar
2010-02-03 17:58:50
@Andomar: You are incorrect. `ExecuteQuery` *appears* to work like `string.Format`, but it does not simply format the string and send the raw SQL to the server. It takes the format string and arguments and generates a parameterized query for you. Imagine if the term `{0}` was written as `@p0` instead - then it would be more obvious. And if you examine the raw query sent to the server, that is actually exactly how it gets translated; `{0}` becomes `@p0`, `{1}` becomes `@p1`, etc.
Aaronaught
2010-02-03 18:07:16
@Aaronaught: You're right. Awesome
Andomar
2010-02-03 18:26:29
A:
Quoting integers in SQL has only a minor performance penalty. Removing the quotes is much less work than converting the ASCII representation to a binary integer.
So I'd say it's perfectly acceptable, especially for a RAD framework.
Andomar
2010-02-03 17:46:07
A:
I'm not sure about all SQL databases, but SQL Server will implicitly convert a quoted number to an int. For example the following returns 166 in SQL Server 2000:
select '500'/3
Mark Ransom
2010-02-03 17:51:04