1?我需要在触发器中调用一个带参数并有返回值的存储过程,如何在触发器中调用并获取他的返回值呢?
2? 如果存储过程返回的不是一个参数,而是Select 出来的一个记录集,那么在触发器中如何操作这个记录集呢? CREATE PROCEDURE [dbo].[p_ExpandBOMForTask]
@BrNo VARCHAR (10),
@SEInterID INT,
@ItemID INT,
@Retstr Float OUTPUT
AS
SET NOCOUNT ON
......
GO
Create Trigger Tri_Insert_POrequestQty
On POrequest
For Insert
As
SET NOCOUNT ON DECLARE @a INT
DECLARE @b INT
DECLARE @c Float
.......
EXEC p_ExpandBOMForTask 0,@a,@b,@c -- ?
GO
2? 如果存储过程返回的不是一个参数,而是Select 出来的一个记录集,那么在触发器中如何操作这个记录集呢? CREATE PROCEDURE [dbo].[p_ExpandBOMForTask]
@BrNo VARCHAR (10),
@SEInterID INT,
@ItemID INT,
@Retstr Float OUTPUT
AS
SET NOCOUNT ON
......
GO
Create Trigger Tri_Insert_POrequestQty
On POrequest
For Insert
As
SET NOCOUNT ON DECLARE @a INT
DECLARE @b INT
DECLARE @c Float
.......
EXEC p_ExpandBOMForTask 0,@a,@b,@c -- ?
GO
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
insert #temp exec p_ExpandBOMForTask.......
--用sp_executesql
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i
select @con
/*-----------
161(1 行受影响)
*/
create table #temp(......)
insert #temp exec p_ExpandBOMForTask.......