June 25, 2009 at 1:43 pm
I was passed a query from a developer...
Basically it creates a temp table, sticks data in it, moves the data from column a to column b and drops column a.
Then it executes that batch and tries to do it again.
Based on my understanding of what's going on, because the column is dropped before the second batch is parsed the second batch throws a parsing error that typecolumn doesn't exist.
Is there a way around this other than dynamic SQL? I realize that Dynamic SQL will get around this because the EXEC statement doesn't get parsed until it's executed and it won't be because the column is dropped.
SET NOCOUNT ON
CREATE TABLE #test
(
id int,
name char(5),
typecode char(1),
newcode tinyint
)
INSERT INTO #test (id, name, typecode)
VALUES (1, 'A', 'C')
INSERT INTO #test (id, name, typecode)
VALUES (2, 'B', 'O')
SELECT * FROM #test
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE
'#test%' AND column_name = 'typecode')
BEGIN
UPDATE #test
SET newcode = CASE typecode
WHEN 'C' THEN 1
WHEN 'O' THEN 2
END
-- Drop the TypeCode column
ALTER TABLE #test DROP COLUMN typecode
END
ELSE
print 'Skipped update #1'
/*
*****************************************
UN-COMMENT THIS GO TO SEE ERROR:
*/
GO
SELECT * FROM #test
-- run this part again to see if it works
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE
'#test%' AND column_name = 'typecode')
BEGIN
UPDATE #test
SET newcode = CASE typecode
WHEN 'C' THEN 4
WHEN 'O' THEN 8
END
-- Drop the TypeCode column
ALTER TABLE #test DROP COLUMN typecode
END
ELSE
print 'Skipped update #2'
SELECT * FROM #test
DROP TABLE #test
SET NOCOUNT OFF
June 26, 2009 at 4:41 am
Try adding the column and then running the create procedure, it should work.
Regards
Gianluca
-- Gianluca Sartori
June 26, 2009 at 8:42 am
Sadly, it's not a procedure.
I'm not in a position to dictate that all queries on the system will go through sprocs... this is SQL thrown at the server. In two stages.
The GO simulates that the first part might be run, the portion after the GO is supposed to run, detect that the column isn't there and then not do anything.
Instead its being parsed and the column isn't there and so the update is causing an error. Even though the update can't run if the column isn't there. What I need to do is just let the procedure run, if it were to run and not parse the update statemnt to see if the column is there there would be no error about the column not existing because if the column doesn't exist, then the update statement run against it couldn't run.
June 26, 2009 at 8:55 am
I think you can't avoid using dynamic SQL.
Well, if you run this statement directly from the program code, it IS dynamic SQL, so you could test for the column on the app side.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply