January 28, 2009 at 8:45 am
When I try to modify a not existing column in a not guilty condition I get an error.
Why is the SQL Server so strict?
Is there any workaround? (I do not want to use EXEC)
Here an example:
if (1 = 0)
begin
update tblTest
set strNewColumn = ''
end
January 28, 2009 at 8:53 am
I'm not 100% sure what you asking for .
The statement you supplied will not run the update? is that what you expect?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 12:34 am
My problem is that even though the condition is false, the sql compiler throws an error for the statement in the condition.
January 29, 2009 at 12:44 am
You're confusing compile-time errors and run-time errors.
At compile time, the entire batch is checked for valid syntax and valid objects. If there's anything wrong, a syntax error is generated. This is the same as any other language. C# will also give me a syntax error if I refer to a variable or class that doesn't exist, even if it's wrapped in an if (1==0) block.
At run time, the code is run and the pieces that execute may cause run-time errors. Code that will never run will never throw a runtime error.
You're getting a syntax error because the column you're referring to doesn't exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 1:36 am
You are right, it's a syntax error at compile time.
Is there any chance to suppress this syntax error?
Do you have a workaround?
January 29, 2009 at 1:49 am
baumgaertner (1/29/2009)
Is there any chance to suppress this syntax error?
No.
Do you have a workaround?
Why do you have an if block that will never be executed in the first place? If it can't be executed (and as written it can't, 1 will never = 0), why is it even there?
p.s. I have a strong feeling of deja-vu. Did you post this exact same question a few months back?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 2:05 am
Yes I post a similar case a few months back.
My problem is the following:
I want to add column and then modify the column in a if-clause.
Theoretical it should be:
if (level = 1)
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
go
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
go
end
But in the if-clause the statement "go" is not possible.
So I must seperate the statements in two if clauses:
if (level = 1)
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
end
go
if (level = 1)
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
And then I have the described problem.
January 29, 2009 at 2:06 am
Thought I remembered this: http://www.sqlservercentral.com/Forums/FindPost613599.aspx
Matt's advice (the last post of that thread) is good. Use dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 2:08 am
What do you mean by dynamic SQL?
I hope not "EXEC".
January 29, 2009 at 2:11 am
baumgaertner (1/29/2009)
What do you mean by dynamic SQL?I hope not "EXEC".
Yes. (or sp_executesql, same thing)
It's that, or break the batch using GO. Your choice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 2:15 am
The second possibility I do not understand:
It's that, or break the batch using GO
Can you describe it?
January 29, 2009 at 2:21 am
baumgaertner (1/29/2009)
Can you describe it?
The way you had it in your last post. Two ifs with a GO in between.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2009 at 2:27 am
Unfortunately this doesn't work:
Here the code:
if (select nDbSubVersion from tblVersion) = 2
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
end
go
if (select nDbSubVersion from tblVersion) = 2
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
The problem is:
When the condition from the if-clause is false, you get the syntax error.
January 29, 2009 at 2:36 am
OK I think I have a solution but it's messy.
But it definitely works from what I can see (but I prob wouldn't use it myself) would be interested to see what others say?
ok so in your batch of code Create a # table and insert 1 row into it.
[Code]
CREATE TABLE #Tmp
(col1 int )
INSERT INTO #Tmp VALUES (1)
[/Code]
Next have your if statement with the alter table and update statement in one if statement separated by a ;
However change the if statement to contain the temp table in an EXISTS statement in the where clause like this:
IF (select nDbSubVersion from tblVersion) = 2
BEGIN
ALTER TABLE tblCustAttrDefValue add strHelpValue varchar(200) null
;
UPDATE tblCustAttrDefValue
SET strHelpValue = RTrim(LTrim(strValue))
WHERE EXISTS (SELECT 1 FROM #tmp)
END
The reason this works from what I understand is something (please correct me if I'm wrong) called deferred naming!
So because sql can see that your statement contains a table that is still to be created (our #table) it will assume that any column in a query the query that doesn't match a table will belong to the # table.
Not sure if that makes sense but I will try and find an article for you as well.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 2:56 am
This solution is really messy.
At first I thought it works.
But when I when a make the following "SELECT", the column "strHelpValue" is missing:
select *
from tblCustAttrDefValue
Any other ideas?
Has anybody experience with the "Business Intelligence Development Studio"?
Are there possibilites which can help me perhaps?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply