存储过程执行顺序问题

[oceanmeng]

我发现我的存储过程在exec AutoInsert执行时最后一步抱错“sum or average aggregate 运算不能以 nvarchar 数据类型作为参数。 ”，进一步看是alter修改字段类型为int没有执行导致的，很奇怪，为何没有执行呢？
我把存储过程所有语句单独copy到查询分析器里面就可以全部按顺序执行，那为何存储过程不行？

CREATE PROCEDURE [dbo].[AutoInsert] AS

SET ANSI_Nulls ON
SET ANSI_WARNINGS ON

drop table BIFS_ATM_INFO;
select * into BIFS_ATM_INFO from  ATM_DEV_INFO 

drop table BIFS_POS_INFO;
select * into BIFS_POS_INFO from POS_DEV_INFO

ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_NUMS int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CWD int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CDM int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CWD_CDM int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Bank int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Space int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Wall int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Hall int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_City int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Rural int;
ALTER TABLE BIFS_POS_INFO ALTER COLUMN  POS_NUMS int;

exec sp_refreshview V_BIFS_ATM_INFO;
exec sp_refreshview V_BIFS_POS_INFO;

drop table BIFS_POS_INFO_GROUP;
select isnull(POSTYPE,'空')POSTYPE,BrandNAME,ModelNAME,sum(POS_NUMS)POS_NUMS,count(distinct CURR_soft_version) AS Soft_Vers,
dbo.FUNC_POSVerInfo(isnull(BrandNAME,'空'),isnull(ModelNAME,'空'),isnull(POSTYPE,'空'))  AS POSVerInfo into BIFS_POS_INFO_GROUP
from  BIFS_POS_INFO where Last_Logon='2016'
group by POSTYPE,BrandNAME,ModelNAME order by  POSTYPE,BrandNAME,POS_NUMS desc;
GO

[卖水果的net]

全部换成动态语句试试；

[道素]

没有重现这个问题 你可以在创建BIFS_POS_INFO时直接创建正确的类型，如：

select *
,CONVERT(INT,POS_NUMS) AS POS_NUMS_INT
into BIFS_POS_INFO 
from POS_DEV_INFO

[好记忆不如烂笔头abc]

数据库啥版本的？

[oceanmeng]

sql server 2000

[中国风]

语句不够简洁改 这样用，不用分多段 ALTER TABLE BIFS_ATM_INFO ALTER COLUMN ATM_NUMS INT,ATM_CWD int.....

[中国风]

查看要用动态

CREATE PROCEDURE [dbo].[AutoInsert] AS

SET ANSI_Nulls ON
SET ANSI_WARNINGS ON

drop table BIFS_ATM_INFO;
select * into BIFS_ATM_INFO from  ATM_DEV_INFO 

drop table BIFS_POS_INFO;
select * into BIFS_POS_INFO from POS_DEV_INFO

ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_NUMS int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CWD int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CDM int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_CWD_CDM int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Bank int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Space int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Wall int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Hall int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_City int;
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN  ATM_Rural int;
ALTER TABLE BIFS_POS_INFO ALTER COLUMN  POS_NUMS int;

exec sp_refreshview V_BIFS_ATM_INFO;
exec sp_refreshview V_BIFS_POS_INFO;

drop table BIFS_POS_INFO_GROUP;
EXEC('select isnull(POSTYPE,''空'')POSTYPE,BrandNAME,ModelNAME,sum(POS_NUMS)POS_NUMS,count(distinct CURR_soft_version) AS Soft_Vers,
dbo.FUNC_POSVerInfo(isnull(BrandNAME,''空''),isnull(ModelNAME,''空''),isnull(POSTYPE,''空''))  AS POSVerInfo into BIFS_POS_INFO_GROUP
from  BIFS_POS_INFO where Last_Logon=''2016''
group by POSTYPE,BrandNAME,ModelNAME order by  POSTYPE,BrandNAME,POS_NUMS desc;')
GO

[学海无涯-回头是岸]

我用临时表的时候 ，存储过程中间用alter 加了 一个字段，后面select 临时表时 ，发现 没有加上那个字段

[oceanmeng]

为何存储过程执行会有跳过某些脚本呢

[中国风]

引用 8 楼 oceanmeng 的回复:

为何存储过程执行会有跳过某些脚本呢

T-SQL 首先会通过数据库引擎 检查语句（语法错误）-》生成执行计划-》 执行计划 你检查语句都不通过，后面的是执行不了的 用动态语句会忽略语法检查