[AccessD] Stored Procedure return value

John:

Here is code I've used to do something similar, except I was looking for a
Sum, not a Min, but it will work similarly. I believe you have to indicate
an output parameter with sp_executesql.
Hope it helps.

James Barash

declare @sql nvarchar(4000)
declare @TotalMailed int

select @sql = 'Select @TotalM=sum(Mailed) from ' + @TblName
execute sp_executesql @sql,N'@TotalM int output', @TotalM = @TotalMailed
output
print 'Total Mailed: ' + cast(@TotalMailed as varchar(15))

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 28, 2009 3:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure return value

OK, my code is as follows:

USE [DataDepot]
GO
/****** Object:  StoredProcedure [dbo].[usp_MinMaxPKID] 
Script Date: 01/28/2009 12:22:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_MinMaxPKID]
	-- Add the parameters for the stored procedure here
	@TblName varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

declare @SQL nvarchar (4000)
declare @MinPK int

	--
	-- Get the min value
	print 'Tbl: ' + @TblName
	begin try
	set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName
	exec sp_executesql @sql, N'@MinPK int', @MinPK
	print 'MinPK=' + cast(@MinPK as varchar)
	end try	
	begin catch
		print 'There was an error getting the min! ' +
ERROR_MESSAGE()
		print @SQL
	end catch

END

The print lines are simple troubleshooting tools.

The first print line prints the table name.  That works.

The second print line is supposed to print the value of the 
found in the @SQL.  It actually prints nothing, which leaves 
me to believe that the value returned is a null.

What am I doing wrong?

It is unbelievable how much time I spend mucking around 
trying to get this stuff to work.

8(



John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
> John,
> 
> Have a look at sp_executesql, which allows you to send the value of an
> output parameter to a variable.
> 
> You could create a sp like this:
> 
> CREATE PROCEDURE usp_MinMaxPKID @tblName varchar(100)
> AS
> DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100);
> SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MinPKID OUTPUT;
> SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName;
> EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MaxPKID OUTPUT;
> SELECT @MinPKID AS MinPKID, @MaxPKID AS MaxPKID;
> GO
> 
> Then execute the sp like this:
> 
> EXEC usp_MinMaxPKID 'MyTable';
> GO
> 
> HTH
> Asger

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com