$$$$$$$$$$$$存储过程传参数出错¥¥¥¥¥¥¥¥¥¥¥¥
--测试表及数据
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stsySalaryItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stsySalaryItem]
GO
CREATE TABLE [dbo].[stsySalaryItem] (
[iSalaryItemNo] [int] IDENTITY (1, 1) NOT NULL ,
[sSalaryItemName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[sSalaryItemXMZD] [sysname] NOT NULL ,
[sFormula] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[iSuitNo] [int] NULL ,
[fItemValue] [real] NULL ,
[iflag] [int] NULL ,
[sFormulaText] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into stsySalaryItem values ('基本工资','JBGZ','JJ*6',1,'101010.0',1,'奖金*6')
insert into stsySalaryItem values('奖金','JJ','JBGZ',1,' 220.0',1,'奖金')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SS_RYWH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SS_RYWH]
GO
CREATE TABLE [dbo].[SS_RYWH] (
[PID] [int] IDENTITY (1, 1) NOT NULL ,
[trName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[trParentID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[trID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[iSuitNo] [int] NULL ,
[DH] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[JBGZ] [int] NULL ,
[YFGZ] [int] NULL ,
[GL] [int] NULL ,
[JJ] [int] NULL
) ON [PRIMARY]
GO
insert into SS_RYWH
select '张三',5,6,3,555555,1200,22,1,1200
--工资计算的存储过程
create proc p_calc
@suitno int --帐套序号(若不传这个参数就没问题)
as
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
,@i1 int,@i2 varchar(20)
select @s1='',@s2='',@s3='',@i1=0
select @i1=@i1+1,@i2='@'+cast(@i1 as varchar)
,@s1=@s1+','+@i2+' varchar(8000)',@s2=@s2+','+@i2+'=''update SS_RYWH set ['+sSalaryItemXMZD+']=('+sFormula+')''',@s3=@s3+'
exec('+@i2+')'
from stsySalaryItem where sFormula is not null and iSuitNo =@suitno
--若不传这个参数就没问题
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
exec('declare '+@s1+'
select '+@s2+'
'+@s3)
GO
--执行层次存储过程,实现工资计算
exec p_calc
--显示计算结果
select * from SS_RYWH
go
--删除测试环境
drop table stsySalaryItem,SS_RYWH
drop proc p_calc