Nested SP
-
I have 2 SP's,the result of the 1st SP shud be passed as parameter to the second SP,can any help me how to do this.
-
if the "result" of sp1 is a variable then just use the output keyword
create proc sp1 @data_in int, @data_out int output
as
set @data_out = @data_in -1
create proc sp2 as
begin
declare @i int, @o int
set @i = 3
exec sp1 @i, @o output
select @o
end
Cheers!
* Noel -
create procedure sp1 @parm1 Int, @parm2 VarChar(25), .... as select @parm1 = col1, @parm2 = col2 from myTable where.... if @@rowcount > 0 begin exec sp2 @parm1, @parm2... end
**ASCII stupid question, get a stupid ANSI !!!** -
3 minutes late....and another perspective ???
**ASCII stupid question, get a stupid ANSI !!!** -
CREATE PROCEDURE dbo.A
(
@PersonnelNumber As Integer
 
as
SELECT e.DisciplineID
FROM Employees e
WHERE
e.PersonnelNumber = @PersonnelNumber
GO
CREATE PROCEDURE dbo.B
(
@DisciplineID As Integer
 
as
SELECT s.SkillName,
s.SkillID
FROM Skills s
WHERE Inactive = 0
AND
(s.DisciplineID = @DisciplineID OR s.DisciplineID=5)
GO
Ok here the result SP A shud be the parameter for SP B
-
Jp, Hopefully you understand this:
CREATE PROCEDURE dbo.A
(
@PersonnelNumber As Integer,
@dis_id int output
)
as
SELECT e.DisciplineID
FROM Employees e
WHERE
e.PersonnelNumber = @PersonnelNumber
GO
--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.B
(
@PersonnelNumber As Integer
)
as
Declare @DisciplineID int
exec dbo.A @PersonnelNumber, @DisciplineID output
SELECT s.SkillName,
s.SkillID
FROM Skills s
WHERE Inactive = 0
AND
(s.DisciplineID = @DisciplineID OR s.DisciplineID=5)
GO
* Noel -
CREATE PROCEDURE dbo.A ( @PersonnelNumber As Integer as Declare @DisciplineID Int SELECT @DisciplineID = e.DisciplineID FROM Employees e WHERE e.PersonnelNumber = @PersonnelNumber if @@rowcount > 0 begin exec dbo.B @DisciplineID end GO CREATE PROCEDURE dbo.B ( @DisciplineID As Integer [Wink] as SELECT s.SkillName, s.SkillID FROM Skills s WHERE Inactive = 0 AND (s.DisciplineID = @DisciplineID OR s.DisciplineID=5) GO
**ASCII stupid question, get a stupid ANSI !!!** -
TGIF - have a great weekend everyone!
**ASCII stupid question, get a stupid ANSI !!!** -
You too!!!!
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply