我发现我的存储过程在exec AutoInsert执行时最后一步抱错“sum or average aggregate 运算不能以 nvarchar 数据类型作为参数。 ”,进一步看是alter修改字段类型为int没有执行导致的,很奇怪,为何没有执行呢?
我把存储过程所有语句单独copy到查询分析器里面就可以全部按顺序执行,那为何存储过程不行?CREATE PROCEDURE [dbo].[AutoInsert] ASSET ANSI_Nulls ON
SET ANSI_WARNINGS ONdrop 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_INFOALTER 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
我把存储过程所有语句单独copy到查询分析器里面就可以全部按顺序执行,那为何存储过程不行?CREATE PROCEDURE [dbo].[AutoInsert] ASSET ANSI_Nulls ON
SET ANSI_WARNINGS ONdrop 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_INFOALTER 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
你可以在创建BIFS_POS_INFO时直接创建正确的类型,如:select *
,CONVERT(INT,POS_NUMS) AS POS_NUMS_INT
into BIFS_POS_INFO
from POS_DEV_INFO
这样用,不用分多段
ALTER TABLE BIFS_ATM_INFO ALTER COLUMN ATM_NUMS INT,ATM_CWD int.....
SET ANSI_WARNINGS ONdrop 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_INFOALTER 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
T-SQL 首先会通过数据库引擎 检查语句(语法错误)-》生成执行计划-》 执行计划你检查语句都不通过,后面的是执行不了的用动态语句会忽略语法检查