running a SP in SP

[junkmail]

I am having a problem with this SP. What I am wanting to do is get a value "@quantity" from one SP and use it in the select of this SP. The problem is that the select that uses the "@quantity does not return my information. I have verified the "@quantity" value and it is correct. If I hard code "@quantity" with a value everything works fine.

@jobnumber varchar(10)
--@quantity int
AS
BEGIN
declare @quantity int
declare @qty int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
exec @quantity = fs_get_qty @jobnumber
-- Insert statements for procedure here
SELECT dbo.estimateqtytable.JobNumber, estimatenumber,sum(stockprice) as estpapercost

FROM dbo.estimateqtytable
WHERE (dbo.estimateqtytable.jobnumber=@jobnumber) and dbo.estimateqtytable.quantity=@quantity
--WHERE (dbo.estimateqtytable.jobnumber=@jobnumber) and dbo.estimateqtytable.quantity=(select OrderedQuantity from dbo.fs_get_qty(@jobnumber))

GROUP BY dbo.estimateqtytable.JobNumber,estimatenumber
END

 

[gmmastros]

Can you show the code for fs_get_qty ?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

here you go.


LTER PROCEDURE [dbo].[fs_get_qty]
-- Add the parameters for the stored procedure here
@jobnumber varchar(10)


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Select top 1
--OH.Jobnumber,
--OC.ComponentNumber,
OQT.Quantity as Quantity

from OrderHeader OH

Inner join OrderQtyTable OQT on OH.JobNumber = OQT.JobNumber
inner join OrderComponent OC on OQT.jobnumber = OC.jobnumber and OQT.ComponentNumber = OC.ComponentNumber and OQT.QuantityLineNo = OC.QTYOrdIndex
where oh.jobnumber=@jobnumber
order by OH.JobNumber
END

 

[gmmastros]

There are several ways to get data from a stored procedure.

1. You can use an output parameter,
2. You can use a return statement
3. You can capture the output with Insert/Exec.



Using an Output Parameters

Your SP would have an additional parameter declared as OUTPUT

Create Procedure TestOutputParameter
  @InputParam varchar(10),
  @OutputParam Int OUTPUT
As

  Set @OutputParam = Len(@InputParam)

You would call it like this:

  Declare @X Int

  Exec TestOutputParameter 'Testing', @X OUTPUT

  Select @X

Using a Return Statement

Industry standard best practices suggest that the return statement should be reserved for a "status" result. Meaning, did the stored procedure execute successfully. Return statements are limited to integers. You should probably not use this method, but here's how it would work.

Create Procedure TestReturnStatement
  @InputParam VarChar(10)
AS

Return (Select Len(@InputParam))

You would execute it like this:

Declare @X Int
Exec @X = TestReturnStatement 'Testing'
Select @X

Capture the Output With Insert/Exec

With this method, you can capture more than just a scalar value. (A scalar is just a single piece of data). When capturing the output of a procedure, the procedure *can* return multiple columns and multiple rows. Basically, you create a table variable or temp table and then insert in to is from the stored procedure, like this:

Create Procedure TestCaptureOutput
  @InputParam VarChar(10)
As
Select Len(@InputParam) As OutputCol1

You would run it like this:

Declare @Temp Table(OutputCol1 Int)

Insert Into @Temp Exec TestCaptureOutput 'Testing'

Select OutputCol1 From @Temp

Your existing stored procedure is selecting the data that you want, so you will need to use the "Capture the output" method.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

Thanks I appreciate your help!