December 4, 2008 at 6:20 am
Hello together,
I have a problem with compilation of SQL Code.
In my code I has two batches with a if-clause.
In the first batch I add a column to a table, in the second batch I write a value in the added column.
Now the problem:
- When the condition of the first if-clause is TRUE -> No Problem
- When the condition of the first if-clause is FALSE -> Problem!!!!
The message is:
> Meldung 207, Ebene 16, Status 1, Zeile 0
Ungültiger Spaltenname 'strHelpValue'.
As workaround I use:
exec('update tblCustAttrDefValue set strHelpValue = RTrim(LTrim(strValue))')
But that's not pretty good.
Has anyone a better solution?
Example:
if (1 = 0)
begin
alter table tblCustAttrDefValue
add strHelpValue varchar(255) null
end
go
if (1 = 0)
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
December 4, 2008 at 7:56 am
You can't put them both within the same IF clause? It's pretty simple really, if one can evaluate and the other can't, you're going to arrive at a logical error. It just makes sense. I don't like this solution, but what about adding the original IF clause above the UPDATE statement so that it only UPDATES if the original IF, which can return negative, returns positive?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2008 at 8:14 am
if you are using this code in stored procedure then plz paste complete sp here
since what problem is there in compliation if field is not exist and u are updating that field then it will give compilation error
December 4, 2008 at 8:16 am
I think the problem with one if statement is the inline GO's for the alter table. I've got a mid procedure alter table in production and it works fine using dynamic sql to do the alter statement... but when I tried a quick test version, it isn't working and I haven't quite figured out why yet.
December 5, 2008 at 12:55 am
It is not possible to put both statements in one if-clause:
if (1 = 0)
begin
alter table tblCustAttrDefValue
add strHelpValue varchar(255) null
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
When you try this you get an error because in the second statement the new column is not yet known.
You have to seperate the statements with a batch.
Any other ideas?
December 5, 2008 at 1:48 am
Call another procedure.
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
December 5, 2008 at 3:35 am
How shall I call another procedure?
What do you mean?
Describe, please.
December 5, 2008 at 4:49 am
Taking one step back... Why do you need to add columns to a table in a stored procedure?
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
December 5, 2008 at 9:00 am
Other question:
is it possible to disable or to configure the checks of the sql compiler?
December 5, 2008 at 9:03 am
No.
But which "checks" are you referring to?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2008 at 9:06 am
When you execute the following sql you get a warning.
But that's really no error!
There must be a possibitly.
if (1 = 0)
begin
alter table tblCustAttrDefValue
add strHelpValue varchar(255) null
end
go
if (1 = 0)
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
December 5, 2008 at 9:41 am
baumgaertner (12/5/2008)
There must be a possibitly.
To do what?
Why do you need to add columns to a table inside a stored procedure?
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
December 5, 2008 at 10:03 am
This is in one of the SP's that I wrote quite a while back, and it works fine.
...
CREATE TABLE #temp_final_results (
... -- A whole bunch of fields, but not the ones added below
...
SET @SQL = 'ALTER TABLE #temp_final_results ADD AuthNumber varchar(50),AuthDatevarchar(50),
ArrivalDate varchar(50),ArrivalDays varchar(50),QuoteDate varchar(50),QuoteDays varchar(50),
CompleteDate varchar(50),CompleteDays varchar(50), ReportType varchar(50)'
Exec(@SQL)
UPDATE #temp_final_results
SETAuthNumber = F.AuthNumber,
...
Simple little tests using this same theory I've tried to use in QA (whether I create a SP with the test code in it or not) fail. Doing this fails:
CREATE PROCEDURE Seth_ACTest
AS
CREATE TABLE #A(A varchar(10))
INSERT INTO #A (A) VALUES('A')
DECLARE @SQL varchar(200)
SET @SQL = 'ALTER TABLE #A ADD varchar(10)'
EXEC(@SQL)
UPDATE #A SET B = 'B' FROM #A WHERE A = 'A'
SELECT * FROM #A
GO
exec Seth_ACTest
RESULT:
Server: Msg 207, Level 16, State 1, Procedure Seth_ACTest, Line 13
Invalid column name 'B'.
No clue why it works in one and not the other.
December 5, 2008 at 10:11 am
Garadin (12/5/2008)
RESULT:Server: Msg 207, Level 16, State 1, Procedure Seth_ACTest, Line 13
Invalid column name 'B'.
No clue why it works in one and not the other.
I ran your second proc on SQL 2005 and 2008 (developer edition). Ran fine on both, no errors, no warnings.
Deferred checking should mean that no checks are done if the table doesn't exist. Is there any chance that you had a temp table #A created on that connection already?
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
December 5, 2008 at 10:18 am
Indeed, I can run it fine on 2K8 as well, our production server is still 2K though, and it fails there.
The other procedure is running fine on the 2K server. Same Server, Same Database.
The only thing I can think of is that the amount of data / size of the query makes the optimizer handle it differently and allows it to go through. The first SP is ~1400 lines compared to 14.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply