Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using sp_execute issue

Status
Not open for further replies.

tbode2006

Programmer
Aug 24, 2006
62
US
Given the following in Query Analyzer:
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top