存储过程包括添加列、更新该列的数据和选择该表中的所有数据 [英] Stored Procedure consist Add column, Update data for that column, and Select all data from that table

问题描述

我写了一个存储过程如下:

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

然后我得到错误:

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

谁能帮我?

谢谢！

推荐答案

在 CREATE 期间解析存储过程时，该列不存在，因此您会收到错误消息.

When the stored proc is parsed during CREATE the column does not exist so you get an error.

逐行运行内部代码是可行的，因为它们是分开的.第二批 (UPDATE) 运行，因为该列存在.

Running the internal code line by line works because they are separate. The 2nd batch (UPDATE) runs because the column exists.

解决此问题的唯一方法是使用动态 SQL 进行更新和选择，以便直到 EXECUTE 时间(而不是像现在这样的 CREATE 时间)才会对其进行解析.

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

然而，这是我真的不会做的事情:DDL 和 DML 在同一位代码中

However, this is something I really would not do: DDL and DML in the same bit of code

这篇关于存储过程包括添加列、更新该列的数据和选择该表中的所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！