sp output params not working in trigger
-
Hi - I have an sp which has an output parameter which is returned correctly when I call the sp 'raw' in Query analyzer, but, when I call that same sp with the same (input & output) parameters in a trigger, it returns the value NULL in the output parameter. I know this (correct values) because I do pre- & post- sp call PRINT statements in the trigger. Does anyone know why on earth this might be happening? It's doing my head in...
SQL Server v7 SP3
Thanks,
GAM
-
Did you remember to specify the output parameter as an output?:
exec proc_name @output_Param = @localVar OUTPUT
This works on sql2000.... I don't know about 7.0
drop table t1
drop table t2
drop procedure usp_test
GO
create table t1 (col1 int)
create table t2 (col1 int)
GO
create procedure usp_test
@output int OUTPUT
AS
set @output = 10
GO
create trigger t1_trigger on [t1]
for INSERT
AS
declare @a int
exec usp_test @output = @a OUTPUT
insert into t2
select @a as Col1
--select * from INSERTED
GO
select * from t1
select * from t2
insert into t1 (col1) select 100 as col1
select * from t1
select * from t2
-
>Did you remember to specify the output parameter as an output?:
Well - I wouldn't be getting the correct (non-NULL) values returned in QA if it wasn't, would I?
>exec proc_name @output_Param = @localVar OUTPUT
I haven't been as explicit with the params in my sp call as this. Mine would be:
exec proc_name @output_param OUTPUT
Could this be a problem?
GAM
-
Have found in the past that i cannot use QA as an application in sense of a testing routine, since it acts completely differently from the other routines that we have to implement. Sorry that this does not help and that my life is a complete failure, but maybe the problem lies elsewhere...
-
Maybe if you implement jraha's example and check that it work as expect in the trigger and then substitute your real code.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply