A. Stam A. Stam - 3 months ago 10
SQL Question

Stored Procedure refuses to delete column that it creates

I've created a Stored Procedure that refreshes the data in a table. It first re-loads the entire table. Next, several filters are applied. (Example: the column 'Model' must equal 'W'; all rows with model 'B' are deleted.) This happens after the table has been loaded (and not during) because I want to log how many rows are deleted because of each individual filter. After the filters have been applied, some columns contain the same value in every row (the other values were deleted in the filtering process). These columns are now useless, so I want to delete them.

This seems to be problematic for SQL Server. When given the command to execute the SP, it indicates that the columns it is supposed to remove in its final step do not currently exist and refuses to run. That is technically correct, the columns currently don't exist, but they will be created by the SP itself.

Some mockup code:

CREATE PROCEDURE dbo.Procedure AS (
DROP TABLE dbo.Table
SELECT * INTO dbo.Table FROM dbo.View
INSERT INTO dbo.Log VALUES (GETDATE(),(SELECT COUNT(1) FROM dbo.Table))
DELETE FROM dbo.Table WHERE Model <> 'W'
INSERT INTO dbo.Log VALUES (GETDATE(),(SELECT COUNT(1) FROM dbo.Table))
ALTER TABLE dbo.Table DROP COLUMN Model
)


Error code when executing:

[2016-09-02 12:25:20] [S0001][207] Invalid column name 'Model'.


How do I circumvent this problem and get the SP to run?

Answer

If I understand correctly, you can use dynamic SQL:

exec sp_executesql 'ALTER TABLE dbo.Table DROP COLUMN Model';