November 30, 2010 at 7:42 am
Can somebody tell me why the SQL below is not behaving as expected? Is this another SQL quirk or is this a case of the DBA (me) sticking his head up his a**?!?! I am expecting that if a column does not exist in my table that it will use the secondary updates. I simplified this to troubleshoot but it still does not behave. I get an invalid column error. (The formatting of the sql does not stay....sorrry)
***************
declare @groupID varchar(20)
If exists (Select * from sys.columns where name in ('CLIENT_ID') and object_id = object_id('ThisDamnTable'))
Begin
If exists(select * from ThisDamnTable where CLIENT_ID = -1 and NSNAME='xyz' and NAME = 'WTF')
Begin
Print 'Updating the ThisDamnTable where CLIENT_ID = -1 and NSNAME=''xyz'' and NAME = ''WTF'''
exec AdminDb..usp_RandomGenerator @groupID OUTPUT
select 'The old value of ' + VALUE+ ' has been changed to ' +@groupID '.'
from ThisDamnTable where CLIENT_ID = -1 and NSNAME='xyz' and NAME = 'WTF'
UPDATE ThisDamnTable SET VALUE = @groupID where CLIENT_ID = -1 and NSNAME='xyz' and NAME = 'WTF'
End
END
Else If exists (Select * from sys.columns where name in ('ID') and object_id = object_id('ThisDamnTable'))
Begin
If exists(select * from ThisDamnTable where ID = -1 and NSNAME='xyz' and NAME = 'WTF')
Begin
Print 'Updating the ThisDamnTable where ID = -1 and NSNAME=''xyz'' and NAME = ''WTF'''
exec AdminDb..usp_RandomGenerator @groupID OUTPUT
select 'The old value of ' + VALUE+ ' has been changed to ' +@groupID '.'
from ThisDamnTable where ID = -1 and NSNAME='xyz' and NAME = 'WTF'
UPDATE ThisDamnTable SET VALUE = @groupID where ID = -1 and NSNAME='xyz' and NAME = 'WTF'
End
END
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 30, 2010 at 9:41 am
My guess is one of the other fields does not exist. You are checking the CLIENT_ID field but you are not checking the other fields. Are you sure the NSName, Name and Value fields exist?
Also can you post the exact error message as that might help pinpoint the issue.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
November 30, 2010 at 9:45 am
The column CLIENT_ID does not exist. That is why I am doint the check for it before hand. In the older version of this database, is is simply ID. I only need to act if the column exists. Since the column does not exists, I am expecting SQL server to simply ignore the code in the block but it does not.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 30, 2010 at 10:16 am
You're thinking strictly of how the procedure runs. The error is being generated at compile time - sql evaluates the statement(s), sees that the column doesn't exist, and throws the error before starting to actually run the procedure.
Ways to get it to work:
1. Utilize dynamic sql (but use it properly so that you don't have sql injection issues)
2. Utilize separate procedures to do each update with different columns.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 30, 2010 at 10:48 am
SQL Server has to make sure every action in every IF can be executed. Your script won't run, because pieces of it can't be run, so you get that error.
IF doesn't work the same way in T-SQL as it does in, for example, VB. If a statement in an IF clause won't compile, you'll get an error, even if the statement won't be reached by the current execution flow.
Either create two separate, valid, stored procedures, and make the IF statement pick which one to execute, or use dynamic SQL.
Better yet, explain what problem you are trying to solve here, and we might be able to help you come up with a better solution that doesn't involve this kind of complication.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:05 pm
I was afraid of that. I was trying to get away with not using any dynamic sql. :: sigh :: I guess i am stuck. If any one has a better way to do it, I will accpet it.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 30, 2010 at 12:07 pm
What is it you're trying to solve? Do you have a table that sometimes has a column and sometimes doesn't? That would be quite odd and unusual in my experience, but it's all I can think of that would make this make sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:11 pm
Old version vs New Version. Not all of my clients have been updated and my developers have not given me a way to check the version so I must check for the column and act accordingly
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 30, 2010 at 12:15 pm
SQL compiles in batches. A "GO" statement will start a different batch. Sometimes you can use that to work around column-exists-or-not issues.
You can also use a stored proc (or a different stored proc, if already in a stored proc) to make the changes if the col exists:
IF EXISTS(....)
BEGIN
EXEC my_proc_based_on_client_id_col_being_there
END
Scott Pletcher, SQL Server MVP 2008-2010
November 30, 2010 at 12:20 pm
And, of course, no source control or DDL logging in the database, right?
The best way to handle it is have the table DDL that adds/removes the pertinent column, be in the same script as the DDL that modifies affected stored procedures. No roll-out to any production systems that aren't inclusive of all needed changes.
If you can't do something like that, you'll be stuck with dynamic SQL of one sort or another. Make sure management knows that there are HUGE problems with not doing ACID DDL updates to the databases, and with not doing version control on production databases. That way, when it inevitably blows up, you can at least say, "I told you so".
And if devs aren't communicating enough to let you know what databases in production have had table modifications made without proc modifications, I'd seriously recommend looking for employment elsewhere as quickly as you can.
At the very least, add DDL logging to the databases, so you can tell when and what was done to modify the structure. That'll give you at least SOME info and control.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:27 pm
I hear ya. I have gotten them to do a lot of things since my tenure here as the DBA. Getting the time to fix other things is an uphill battle! :: grin :: This particulare piece will be dynamic. :: sigh :: I did get the version put back into a table but it wont happen until the next release and wont help for this particular issue. I did just manage to corner them and get a commitment that the column in question is an unneeded parameter. I wish I had known that before I started this quest.
THanks for all the input. Have a Merry Christmas!
**** no further updates are needed. ****
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 30, 2010 at 12:27 pm
Note that you could have a script that:
1) created stored procs that did the update(s)
2) main code that ran or not ran each stored proc as needed based on column(s) present/not present
3) delete the stored procs created at the start.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply