Thursday, March 29, 2012

EXECuting sps within sps (using return params)

Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @.@.IDENTITY) as input for some of the other procedures.

I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...


CREATE PROCEDURE addTemplateDetail
@.TemplateID int,
@.GroupNameID int,
@.SubGroupNameID int=null,
@.MethodID int,
@.AnalyteID int
AS
DECLARE @.TemplateGroupNameID int
DECLARE @.TemplateMethodID int
SET @.TemplateGroupNameID=(EXEC addTemplateGroupName @.TemplateID, @.GroupNameID)
SET @.TemplateMethodID=(EXEC addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID)
EXEC addTemplateAnalyte(@.TemplateMethodID,@.AnalyteID)

I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.Never mind.
I changed the two lines with return params to:

EXEC @.TemplateGroupNameID=addTemplateGroupName @.TemplateID, @.GroupNameID
EXEC@.TemplateMethodID=addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID

Hope that will somebody else too...sql

No comments:

Post a Comment