我的存储过程是否按顺序执行？

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.

简史：我正在编写一个存储过程来支持遗留Web应用程序上的旧报告系统（使用SQL Server Reporting Services 2000）。为了与原始实现样式保持一致，每个报告在数据库中都有一个专用存储过程，该过程执行返回“最终”数据集所需的所有查询，该数据集只能由报表服务器呈现。

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).

由于此报告的业务要求，返回的数据集具有未知数量的列（这取决于执行报告的用户，但可能有4-30列）。

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:

在整个存储过程中，我保留一列UserID来跟踪用户的ID以执行其他查询。但最后，我做了类似这样的事情：

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.

实际上，我将Name varchar列（在我执行某些枢轴逻辑时先前保留为NULL）设置为纯文本中所需的名称格式。

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

完成后，我想删除UserID列，因为报表用户不应该看到这一点。

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.

最后，返回的数据集有一列用于用户名，以及具有性能总数的任意数量的INT列。出于这个原因，我不能简单地排除UserID列，因为SQL不支持“SELECT * EXCEPT [UserID]”等。

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.

但是，如果我注释掉我的DROP COLUMN语句并保留UserID，则存储过程将正确执行。

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:

[编辑1]我之前定义了UserID（整个存储过程大约是200个谎言，大部分是不相关的逻辑，所以我将粘贴片段：

    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.

区分大小写不是问题，但确实指向了正确的行 - 有一个地方我有userID而不是UserID。现在我修复了案例，错误消息抱怨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.

我的“破损”存储过程也可以在SQL Server 2008中正常运行 - 这可能是一个2000错误，或者我严重误解了SQL Server以前的工作方式。

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:

对于将来搜索此内容的任何人，我添加了一个非常粗略的解决方法，以便在我们更新生产版本之前兼容2000：

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!

感谢大家！

4 个解决方案

#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.

它为您说无效列。您是否检查了拼写并确保临时表中甚至存在该列。

#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.

有各种各样的理由说明为什么你不应该从你的程序返回select *。

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?

根据错误消息，数据库区分大小写，那么userID和UserID之间有区别吗？

#3

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

您可以尝试在BEGIN ... COMMIT事务中包装DROP COLUMN之前的所有内容。

#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.

在编译时，SQL Server可能会将*扩展为完整的列列表。因此，在运行时，SQL Server执行“SELECT UserID，Name，LastName，FirstName，...”而不是“SELECT *”。将最终的SELECT动态组装成一个字符串，然后在存储过程结束时执行它可能是要走的路。

#1