参数 @DepartmentID,@ID=,1,2,3,4
把A表的内容根据@ID 读取A表的sum(a),sum(b),sum(c)插入B表 把DepartmentID 也插入B表 这样的存储过程怎么写
create proc insertB
@DepartmentID int,
@ID varchar(20)
as
select @DepartmentID,sum(a),sum(b),sum(c) into B from A where ID in ( @ID )
create proc insertB
@DepartmentID int,
@ID varchar(20)
as
if substring(@ID,0,1)=',' set @ID=right(@ID,len(@ID)-1)
declare @sql varchar(100)
set @sql=N'select @DepartmentID,sum(a),sum(b),sum(c) into B from A where ID in ('+ @ID +')'
exec @sql
--創建存儲過程
Create ProceDure SP_TEST(@DepartmentID Int,@ID Varchar(100))
As
Insert B Select @DepartmentID, a, b, c From A Where ',' + @ID + ',' Like '%,' + Cast(ID As Varchar) + ',%'
GO
--調用
EXEC SP_TEST 1, '1,2,3,4'
--創建存儲過程
Create ProceDure SP_TEST(@DepartmentID Int,@ID Varchar(100))
As
Insert B Select @DepartmentID, a, b, c From A Where CharIndex(',' + Cast(ID As Varchar) + ',', ',' + @ID + ',') > 0
GO
--調用
EXEC SP_TEST 1, '1,2,3,4'
CREATE PROCEDURE dbo.B_Add
@DepartmentID int,
@IDList varchar(2000)
AS
DECLARE @sql varchar(4000)
SET @sql = 'insert into B select sum(a),sum(b),sum(c) from A where ID in('+@IDList+')'
EXEC(@sql)
/*
过程说明:从表B增加数据
创建人:几米天空
创建时间:2007年8月29日
*/
CREATE PROCEDURE dbo.B_Add
@DepartmentID int,
@IDList varchar(2000)
AS
DECLARE @sql varchar(4000)
----加在这
SET @sql = 'insert into B select @DepartmentID,sum(a),sum(b),sum(c) from A where ID in('+@IDList+')'
EXEC(@sql)
----------------------------
加一句
set @IDList = substring(@IDList, 2, len(@IDList)-1)