存储过程代码：
USE [HLJSXKJSYYJSGLPT]
GO
/****** Object: StoredProcedure [dbo].[GetSSYJSFZYLYXSSTJ] Script Date: 03/24/2012 10:30:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetSSYJSFZYLYXSSTJ]
@xxkyjg nvarchar(20),@tjny char(6)
AS
begin
declare @sql nvarchar(200);
set @sql = 'insert into [email protected]+'_SS_TJ
select
COUNT(case when jbxxb.HXWRQ IS NOT NULL then 1 else 0 end) as 授予学位数,
COUNT(case when jbxxb.RXNY >= [email protected]+' then 1 else 0 end) as 招生数合计,
COUNT(case when substring(RXNY,1,4)=substring([email protected]+',1,4) then 1 else 0 end) as 招生应届生数,
COUNT(case when CAST(substring(RXNY,1,4) AS INT)+1 = substring([email protected]+',1,4) then 1 else 0 end) as 在校一年级生数,
COUNT(case when CAST(substring(RXNY,1,4) AS INT)+2 = substring([email protected]+',1,4) then 1 else 0 end) as 在校二年级生数,
COUNT(case when CAST(substring(RXNY,1,4) AS INT)+3 = substring([email protected]+',1,4) then 1 else 0 end) as 在校三年级生数,
COUNT(case when CAST(substring(RXNY,1,4) AS INT) > substring([email protected]+',1,4) then 1 else 0 end) as 在校生合计,
COUNT(case when CAST(substring(RXNY,1,4) AS INT)+3 = substring([email protected]+',1,4) then 1 else 0 end) as 预计毕业生数,
xwlx.XWLX as 学位类型, pyfs.PYFS as 评优方式, xb.XB as 性别, zy.ZY as 专业
from [email protected]+'_SS jbxxb
inner join T_XWLX xwlx on jbxxb.XWLXM=xwlx.XWLXM
inner join T_PYFS pyfs on jbxxb.PYFSM=pyfs.PYFSID
inner join T_XB xb on jbxxb.XBM=xb.XBM
inner join T_ZY zy on jbxxb.ZYDM=zy.ZYM
group by xwlx.XWLX, pyfs.PYFS, xb.XB, zy.ZY';
EXEC(@sql);
END
网站运行时出错提示：在应使用条件的上下文(在 'substr' 附近)中指定了非布尔类型的表达式。
按我自己的各种测试之后的推断，错就出在语句拼合substring传参这块。
------解决方案--------------------
- SQL code
ALTER PROCEDURE [dbo].[GetSSYJSFZYLYXSSTJ] @xxkyjg nvarchar(20),@tjny char(6)ASbegin declare @sql nvarchar(200); set @sql = 'insert into T_YJSXX_' + @xxkyjg + '_SS_TJ select COUNT(case when jbxxb.HXWRQ IS NOT NULL then 1 else 0 end) as 授予学位数,COUNT(case when jbxxb.RXNY >= ''' + @tjny + ''' then 1 else 0 end) as 招生数合计,COUNT(case when substring(RXNY,1,4)=substring([email protected]+',1,4) then 1 else 0 end) as 招生应届生数,COUNT(case when CAST(substring(RXNY,1,4) AS INT)+1 = substring([email protected]+',1,4) then 1 else 0 end) as 在校一年级生数,COUNT(case when CAST(substring(RXNY,1,4) AS INT)+2 = substring([email protected]+',1,4) then 1 else 0 end) as 在校二年级生数,COUNT(case when CAST(substring(RXNY,1,4) AS INT)+3 = substring([email protected]+',1,4) then 1 else 0 end) as 在校三年级生数,COUNT(case when CAST(substring(RXNY,1,4) AS INT) > substring([email protected]+',1,4) then 1 else 0 end) as 在校生合计,COUNT(case when CAST(substring(RXNY,1,4) AS INT)+3 = substring([email protected]+',1,4) then 1 else 0 end) as 预计毕业生数,xwlx.XWLX as 学位类型, pyfs.PYFS as 评优方式, xb.XB as 性别, zy.ZY as 专业 from [email protected]+'_SS jbxxb inner join T_XWLX xwlx on jbxxb.XWLXM=xwlx.XWLXMinner join T_PYFS pyfs on jbxxb.PYFSM=pyfs.PYFSIDinner join T_XB xb on jbxxb.XBM=xb.XBMinner join T_ZY zy on jbxxb.ZYDM=zy.ZYMgroup by xwlx.XWLX, pyfs.PYFS, xb.XB, zy.ZY'EXEC(@sql);END