Given the following in Query Analyzer:
Calling the following Stored Procedure:
This all works. Now I have written in Query Analyzer:
This works in Query Analyzer. My problem is making this into a stored prodedure and the parameter passing. I would like to be able to call this from QA as:
What am I missing in the CREATE PROCEDURE setup for the parameters?
Code:
DECLARE @f datetime,
@t datetime,
@c int,
@a int
SET @f = '19980801'
SET @t = '19980901'
EXEC Batch_Stuff @f, @t, @c OUTPUT, @a OUTPUT
Calling the following Stored Procedure:
Code:
CREATE PROCEDURE [dbo].[Batch_Stuff]
@fromdate datetime,
@todate datetime,
@bCNT int OUTPUT,
@bAVG int OUTPUT
AS
SELECT @bCNT =count(*), @bAVG = AVG(Batch_Wgt) from dbo.BATCH_SEQUENCE
WHERE(START_DATE BETWEEN @fromdate AND @todate)
GO
This all works. Now I have written in Query Analyzer:
Code:
DECLARE @tCNT int,
@tAVG int,
@sql nvarchar(4000),
@params nvarchar(4000)
SELECT @sql =
N'SELECT @cnt =count(*), @avg = AVG(Batch_Wgt) from dbo.BATCH_SEQUENCE' +
N' WHERE(START_DATE BETWEEN @fromdate AND @todate)'
SELECT @params = N'@fromdate datetime, ' +
N'@todate datetime, ' +
N'@cnt int OUTPUT, ' +
N'@avg int OUTPUT'
EXEC sp_executesql @sql, @params, '19980801', '19981001', @cnt = @tCNT OUTPUT, @avg = @tAVG OUTPUT
This works in Query Analyzer. My problem is making this into a stored prodedure and the parameter passing. I would like to be able to call this from QA as:
Code:
exec "name" '19980801', '19980901', @c OUTPUT, @a OUTPUT
What am I missing in the CREATE PROCEDURE setup for the parameters?