Is my stored procedure executing out of order?

Question

Brief history: I'm writing a stored procedure to support a legacy reporting system (using SQL Server Reporting Services 2000) on a legacy web application. In keeping with the original implementation style, each report has a dedicated stored procedure in the database that performs all the querying necessary to return a "final" dataset that can be rendered simply by the report server.

Due to the business requirements of this report, the returned dataset has an unknown number of columns (it depends on the user who executes the report, but may have 4-30 columns).

Throughout the stored procedure, I keep a column UserID to track the user's ID to perform additional querying. At the end, however, I do something like this:

UPDATE #result
SET Name = ppl.LastName + ', ' + ppl.FirstName
FROM #result r
LEFT JOIN Users u ON u.id = r.userID
LEFT JOIN People ppl ON ppl.id = u.PersonID

ALTER TABLE #result
DROP COLUMN [UserID]

SELECT * FROM #result r ORDER BY Name

Effectively I set the Name varchar column (that was previously left NULL while I was performing some pivot logic) to the desired name format in plain text.

When finished, I want to drop the UserID column as the report user shouldn't see this.

Finally, the data set returned has one column for the username, and an arbitrary number of INT columns with performance totals. For this reason, I can't simply exclude the UserID column since SQL doesn't support "SELECT * EXCEPT [UserID]" or the like.

With this known (any style pointers are appreciated but not central to this problem), here's the problem:

When I execute this stored procedure, I get an execution error:

Invalid column name 'userID'.

However, if I comment out my DROP COLUMN statement and retain the UserID, the stored procedure performs correctly.

What's going on? It certainly looks like the statements are executing out of order and it's dropping the column before I can use it to set the name strings!

[Edit 1] I defined UserID previously (the whole stored procedure is about 200 lies of mostly irrelevant logic, so I'll paste snippets:

    CREATE TABLE #result ([Name] NVARCHAR(256), [UserID] INT);

Case sensitivity isn't the problem but did point me to the right line - there was one place in which I had userID instead of UserID. Now that I fixed the case, the error message complains about UserID.

My "broken" stored procedure also works properly in SQL Server 2008 - this is either a 2000 bug or I'm severely misunderstanding how SQL Server used to work.

Thanks everyone for chiming in!

For anyone searching this in the future, I've added an extremely crude workaround to be 2000-compatible until we update our production version:

DECLARE @workaroundTableName NVARCHAR(256), @workaroundQuery NVARCHAR(2000)
SET @workaroundQuery = 'SELECT [Name]';
DECLARE cur_workaround CURSOR FOR
SELECT COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.Columns WHERE TABLE_NAME LIKE '#result%' AND COLUMN_NAME <> 'UserID'
OPEN cur_workaround;
FETCH NEXT FROM cur_workaround INTO @workaroundTableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @workaroundQuery = @workaroundQuery + ',[' + @workaroundTableName + ']'
    FETCH NEXT FROM cur_workaround INTO @workaroundTableName
END
CLOSE cur_workaround;
DEALLOCATE cur_workaround;
SET @workaroundQuery = @workaroundQuery + ' FROM #result ORDER BY Name ASC'
EXEC(@workaroundQuery);

Thanks everyone!

Answer 1

This works for me:

CREATE TABLE #temp_t
 (
  myInt int,
  myUser varchar(100)
 )

INSERT INTO #temp_t(myInt, myUser) VALUES(1, 'Jon1')
INSERT INTO #temp_t(myInt, myUser) VALUES(2, 'Jon2')
INSERT INTO #temp_t(myInt, myUser) VALUES(3, 'Jon3')
INSERT INTO #temp_t(myInt, myUser) VALUES(4, 'Jon4')

ALTER TABLE #temp_t
DROP Column myUser

SELECT * FROM #temp_t

DROP TABLE #temp_t

It says invalid column for you. Did you check the spelling and ensure there even exists that column in your temp table.

Answer 2

A much easier solution would be to not drop the column, but don't return it in the final select.

There are all sorts of reasons why you shouldn't be returning select * from your procedure anyway.

EDIT: I see now that you have to do it this way because of an unknown number of columns.

Based on the error message, is the database case sensitive, and so there's a difference between userID and UserID?

Answer 3

You might try wrapping everything preceding the DROP COLUMN in a BEGIN...COMMIT transaction.

Answer 4

At compile time, SQL Server is probably expanding the * into the full list of columns. Thus, at run time, SQL Server executes "SELECT UserID, Name, LastName, FirstName, ..." instead of "SELECT *". Dynamically assembling the final SELECT into a string and then EXECing it at the end of the stored procedure may be the way to go.