December 5, 2008 at 10:21 am
My curiosity aside, the dynamic SQL alter statement may work for you baumgaertner, as it is only having issues on a 2000 server, and I'm assuming you're using 2005.
December 5, 2008 at 10:23 am
GilaMonster (12/5/2008)
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?
I hit that once or twice, but I explicity dropped it before running the SP, and switched connections a few times and tried exec'ing from them as well. The SP creates, it just won't exec.
December 5, 2008 at 10:25 am
I see why.
It's the insert into A straight after the creation of the temp table. That forces a recompile of the entire proc (because the temp table didn't exist when the proc was first compiled). When that recompile happens, the table #A exists, and so the columns can be checked for existence. Since column B doesn't exist at that point, you get the error.
Try moving the INSERT INTO #A (A) VALUES('A') to after the ALTER TABLE and see if that works.
SQL 2005 and 2008 have statement-level recompile, so they only recompile the statement that refers to column B after the ALTER has happened.
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:31 am
That works. However, in the large SP, the logic goes:
Create Temp Table
...
Insert into Temp Table
Update a bunch of fields in the temp table for various reasons
...
Alter Temp Table if certain condition is met via Dynamic SQL statement
Immediately Update (Populate) Added Columns.
Shouldn't this one hit the same issue?
December 5, 2008 at 10:55 am
Garadin (12/5/2008)
Shouldn't this one hit the same issue?
It should. No idea offhand why it isn't.
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 11:20 am
Hi!!
This works, but you should take care in the first evaluation, this should enter once
Create table #tblCustAttrDefValue(a int, b varchar(10), strValue nvarchar(5))
insert into #tblCustAttrDefValue
Select 1, 'oo', 'vamos'
declare @var int
select @var = 0
if (@var = 0)
begin
alter table #tblCustAttrDefValue
add strHelpValue varchar(255) null
end
else if (@var = 1)
begin
exec('update #tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))')
end
December 8, 2008 at 1:16 am
It'a misunderstanding.
I do not use a stored procedure.
December 8, 2008 at 1:07 pm
This isn't a procedure, I dont' know where are you traying to execute it, but if you execute the next lines in SQL should work...
Create table #tblCustAttrDefValue(a int, b varchar(10), strValue nvarchar(5))
insert into #tblCustAttrDefValue
Select 1, 'oo', 'vamos'
Select * from #tblCustAttrDefValue -- it has not the new column
declare @var int
select @var = 0
if (@var = 0)
begin
alter table #tblCustAttrDefValue
add strHelpValue varchar(255) null
end
else if (@var = 1)
begin
exec('update #tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))')
end
Select * from #tblCustAttrDefValue -- it has the new column
December 8, 2008 at 1:14 pm
baumgaertner (12/5/2008)
How shall I call another procedure?What do you mean?
Describe, please.
It looks to me the easiest way owuld be to put the second if inside of a SQL variable statement you execute. By doing that - it won't deal with the second statement until it runs into the EXEC (i.e AFTER the column is created)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply