| Author |
Topic |
|
vooose
Starting Member
10 Posts |
 Posted - 2004-12-02 : 01:59:52
|
Consider a simple Table, we'll call it TempTable, and it has two columns, Key and Value. Now I know what you're already saying - 'Don't use Key and Value, those are reserved words!' but lets just assume its a bit late for that now and theres nothing we can do.  Lets add a row of data, INSERT INTO TempTable ([Key], [Value]) VALUES ('key', 'value')All works grand. Now suppose we want to update... we can doUPDATE TempTable SET Value='newval' WHERE [Key]='key'which again works fine.What I am trying to understand is why Value did not have to be escaped[] in order for this to work. If you leave the [ ] off Key the statement fails syntax. Recently I encountered a problem in one of my stored procedures...I debugged and debugged, stepped over line by line and noticed that a line was attemtping to UPDATE in a manner similar to the above (that is, using the [] on Key but not on [Value]). The UPDATE didn't occur and even worse I got no errors! Everything was fixed when I changed Value to [Value], but this isnt consistent with my simple experiment above.Any comments? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-02 : 07:52:29
|
Well, consider this: if adding the brackets always works, then always include the brackets. Knowing why something behaves strangely usually isn't worthwhile. You want to know that your code works reliably, not that it takes advantage of some odd quirk that really doesn't do anything helpful.Of course, you wouldn't have this problem if you didn't have reserved words as column/object names... |
 |
|
|
vooose
Starting Member
10 Posts |
Posted - 2004-12-02 : 16:04:35
|
Thanks for your reply. To add a bit more spice, I noticed that it works (without brackets) on one sql server, and then fails to work on another until I restore over the non-working one with the working one.>Knowing why something behaves strangely usually isn't worthwhileDon't those kind of problems keep you up all night wondering what the hell is going on?  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-02 : 16:11:10
|
| Key is a reserved word and value is not. Values however is. I try to avoid reserved words for objects names.Is this MS SQL Server? I'm curious as if it fails on one box, it should fail on the other if it's MS SQL Server at least.Tara |
|
|
|
vooose
Starting Member
10 Posts |
Posted - 2004-12-03 : 02:29:58
|
| Yes sorry I incorrectly stated that Value was a reserved word, when it isn't. However escaping [Value] seemed to do the trick on the box that wasnt working. They are both running MS SQL Server. It doesnt make sense, as I see it, that it could work on one box and not the other, given the stored procedures are *exactly* the same. It was only when a restore was done over the top of the faulty DB which corrected the problem. Before this restore, I can guarantee 100% that the stored procedures were identical. Very strange, although the problem has been solved (worked around I guess) I would still like to know what caused the discrepency. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-03 : 02:43:34
|
did you mean this didn't workquote:
INSERT INTO TempTable ([Key], Value) VALUES ('key', 'value')
but this did?INSERT INTO TempTable ([Key], [Value]) VALUES ('key', 'value')--------------------keeping it simple... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 07:52:02
|
| Are you sure that the DB that wasn't working wasn't set to a different compatibility mode? Was the database upgraded from an earlier version of SQL Server? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-03 : 19:22:18
|
| Are you sure that you are using SINGLE quotes around your data elements and not DOUBLE quotes (e.g., ('key', 'value') and not ("key", "value"))?The double quotes could easily work differently on two different servers.IMHO, you always want to know WHY things are behaving the way they are. If you don't, you can easily still have a problem that has not been uncovered YET.HTH |
|
|
|
vooose
Starting Member
10 Posts |
Posted - 2004-12-05 : 17:42:17
|
| Jen, what you have said is correct - Those 2 statements worked fine on one sql installation, but the former failed on a second installation. robvolk, both are sql server 2000, not upgraded, however how can I check the compatibility mode(s)? That I believe must be the difference here.Bustaz Kool: using 'key' and 'value' as the contents was probably a confusing way. I should have used 'val1' and 'val2' just so it was unambigious--but yes I was using single quotations. |
|
|
|
|
UPDATE in a stored procedure using Key,Value