Stored Procedure consist Add column, Update data for that column, and Select all data from that table

Question

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!

Answer 1

When I run

IF not exists (select column_name from  INFORMATION_SCHEMA.columns where
                table_name = 'NhanVien' and   column_name = 'SoNguoiThan')  
            ALTER TABLE NhanVien ADD   SoNguoiThan int

and then I run Update Statement and Select :

UPDATE  NhanVien
            SET  NhanVien.SoNguoiThan = (SELECT  Count(MaNguoiThan)FROM NguoiThan
                                         WHERE MaNV=NhanVien.MaNV 
                                         GROUP BY  NhanVien.MaNV)   

SELECT *
    FROM NhanVien 
WHERE    SoNguoiThan>@SNT

then no error.

Can you help me?

Answer 2

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