存储过程包括添加列、更新该列的数据和从该表中选择所有数据
我编写了一个存储过程，如下所示：
CREATE PROC spSoNguoiThan
@SNT int
AS
begin
IF not exists (select column_name from INFORMATION_SCHEMA.columns where
table_name = 'NhanVien' and column_name = 'SoNguoiThan')
ALTER TABLE NhanVien ADD SoNguoiThan int
else
begin
UPDATE NhanVien
SET NhanVien.SoNguoiThan = (SELECT Count(MaNguoiThan)FROM NguoiThan
WHERE MaNV=NhanVien.MaNV
GROUP BY NhanVien.MaNV)
end
SELECT *
FROM NhanVien
WHERE SoNguoiThan>@SNT
end
GO
然后我收到错误：
Server: Msg 207, Level 16, State 1, Procedure spSoNguoiThan, Line 12
Invalid column name 'SoNguoiThan'.
Server: Msg 207, Level 16, State 1, Procedure spSoNguoiThan, Line 15
Invalid column name 'SoNguoiThan'.
谁可以帮助我？
谢谢！
I've written a stored procedure as following:
CREATE PROC spSoNguoiThan
@SNT int
AS
begin
IF not exists (select column_name from INFORMATION_SCHEMA.columns where
table_name = 'NhanVien' and column_name = 'SoNguoiThan')
ALTER TABLE NhanVien ADD SoNguoiThan int
else
begin
UPDATE NhanVien
SET NhanVien.SoNguoiThan = (SELECT Count(MaNguoiThan)FROM NguoiThan
WHERE MaNV=NhanVien.MaNV
GROUP BY NhanVien.MaNV)
end
SELECT *
FROM NhanVien
WHERE SoNguoiThan>@SNT
end
GO
Then I get the error :
Server: Msg 207, Level 16, State 1, Procedure spSoNguoiThan, Line 12
Invalid column name 'SoNguoiThan'.
Server: Msg 207, Level 16, State 1, Procedure spSoNguoiThan, Line 15
Invalid column name 'SoNguoiThan'.
Who can help me?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当在 CREATE 期间解析存储过程时，该列不存在，因此会出现错误。
逐行运行内部代码是有效的，因为它们是分开的。由于该列存在，因此运行第二批（更新）。
解决这个问题的唯一方法是使用动态 SQL 进行更新和选择，这样直到 EXECUTE 时间（而不是像现在这样的 CREATE 时间）才对其进行解析。
然而，这是我真的不会做的事情：DDL 和 DML 在同一位代码中
When the stored proc is parsed during CREATE the column does not exist so you get an error.
Running the internal code line by line works because they are separate. The 2nd batch (UPDATE) runs because the column exists.
The only way around this would be to use dynamic SQL for the update and select so it's not parsed until EXECUTE time (not CREATE time like now).
However, this is something I really would not do: DDL and DML in the same bit of code
我遇到了同样的问题，发现除了使用动态 sql 之外，我还可以通过交叉连接到只有一行的临时表来解决它。这导致脚本编译器在编译时不会尝试解析重命名的列。下面是我在不使用动态 SQL 的情况下解决问题的示例
I ran into this same issue and found that in addition to using dynamic sql I could solve it by cross joining to a temp table that had only one row. That caused the script compiler to not try to resolve the renamed column at compile time. Below is an example of what I did to solve the issue without using dynamic SQL