或者也可以用openrowset,比如vb可以这样来写: dim strsql as string strsql= 'insert EmpMonthSal SELECT empid,salDate='''& txtDate.text &''',PosSal,YearSal FROM OPENROWSET('SQLOLEDB','SQL服务器名称';'用户名';'密码', 'exec GetMonthSalary') AS a--然后用recordset对象执行上边的SQL语句就可以了,txtDate是你的时间输入控件
--做了一个测试 --创建表SalSet create table SalSet(postbase decimal,yearbase decimal) insert SalSet select 450,10 --创建EmpSal表 create table EmpSal(empid char(3),postxs decimal,workyear int) insert EmpSal select '001', 2.3, 3 union all select '002', 4.0, 5 union all select '003', 1.8, 2 --创建存储过程 CREATE PROCEDURE GetMonthSalary AS declare @YearBase decimal declare @PostBase decimal select @YearBase=[YearBase] from SalSet select @PostBase=[PostBase] from SalSet select empid,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal go --创建EmpMonthSal表 create table EmpMonthSal(empid char(3),salDate datetime,PosSal decimal,YearSal decimal) --插入数据 insert EmpMonthSal(empid,PosSal,YearSal) SELECT empid,PosSal,YearSal FROM OPENROWSET('SQLOLEDB','mschen';'sa';'3234461', 'exec GetMonthSalary') AS a --插入成功 /* empid salDate PosSal YearSal ----- ------------------------------------------------------ -------------------- -------------------- 001 NULL 900 30 002 NULL 1800 50 003 NULL 900 20 */
谢谢两位,因为我用的是asp.net,C#语言,每一种方法生成的工资是生成时刻的,假设用户想 在这个月生成下个月的工资,就不适用了.每二种方法,因为我对vb不熟悉,而且我所有 的方法都是用存储过程写的.我想了一个办法,在存储过程 CREATE PROCEDURE GetMonthSalary AS declare @YearBase decimal declare @PostBase decimal select @YearBase=[YearBase] from SalSet select @PostBase=[PostBase] from SalSet select empid,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal end go 加上一个时间参数,@SalDate 如下: CREATE PROCEDURE GetMonthSalary declare @SalDate datetime AS declare @YearBase decimal declare @PostBase decimal select @YearBase=[YearBase] from SalSet select @PostBase=[PostBase] from SalSet select empid,@SalDate,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal end go 却提示有语法错误,怎样在这里传一个用户指定的时间参数进去?
自己搞糊涂了.很简单, CREATE PROCEDURE GetMonthSalary @SalDate datetime AS declare @YearBase decimal declare @PostBase decimal select @YearBase=[YearBase] from SalSet select @PostBase=[PostBase] from SalSet select empid,@SalDate,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal end go
dim strsql as string
strsql=
'insert EmpMonthSal
SELECT empid,salDate='''& txtDate.text &''',PosSal,YearSal
FROM OPENROWSET('SQLOLEDB','SQL服务器名称';'用户名';'密码',
'exec GetMonthSalary') AS a--然后用recordset对象执行上边的SQL语句就可以了,txtDate是你的时间输入控件
--创建表SalSet
create table SalSet(postbase decimal,yearbase decimal)
insert SalSet
select 450,10
--创建EmpSal表
create table EmpSal(empid char(3),postxs decimal,workyear int)
insert EmpSal
select '001', 2.3, 3
union all select '002', 4.0, 5
union all select '003', 1.8, 2
--创建存储过程
CREATE PROCEDURE GetMonthSalary
AS
declare @YearBase decimal
declare @PostBase decimal
select @YearBase=[YearBase] from SalSet
select @PostBase=[PostBase] from SalSet
select empid,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal
go
--创建EmpMonthSal表
create table
EmpMonthSal(empid char(3),salDate datetime,PosSal decimal,YearSal decimal)
--插入数据
insert EmpMonthSal(empid,PosSal,YearSal)
SELECT empid,PosSal,YearSal
FROM OPENROWSET('SQLOLEDB','mschen';'sa';'3234461',
'exec GetMonthSalary') AS a
--插入成功
/*
empid salDate PosSal YearSal
----- ------------------------------------------------------ -------------------- --------------------
001 NULL 900 30
002 NULL 1800 50
003 NULL 900 20
*/
在这个月生成下个月的工资,就不适用了.每二种方法,因为我对vb不熟悉,而且我所有
的方法都是用存储过程写的.我想了一个办法,在存储过程
CREATE PROCEDURE GetMonthSalary
AS
declare @YearBase decimal
declare @PostBase decimal
select @YearBase=[YearBase] from SalSet
select @PostBase=[PostBase] from SalSet
select empid,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal
end
go
加上一个时间参数,@SalDate
如下:
CREATE PROCEDURE GetMonthSalary
declare @SalDate datetime
AS
declare @YearBase decimal
declare @PostBase decimal
select @YearBase=[YearBase] from SalSet
select @PostBase=[PostBase] from SalSet
select empid,@SalDate,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal
end
go
却提示有语法错误,怎样在这里传一个用户指定的时间参数进去?
CREATE PROCEDURE GetMonthSalary
@SalDate datetime
AS
declare @YearBase decimal
declare @PostBase decimal
select @YearBase=[YearBase] from SalSet
select @PostBase=[PostBase] from SalSet
select empid,@SalDate,@PostBase*postxs as PosSal,@YearBase*workyear as YearSal from EmpSal
end
go