[AccessD] Stored Procedure return value

John,

Change the two statements:

set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName
exec sp_executesql @sql, N'@MinPK int', @MinPK

To:

set @SQL = N'SELECT @MinPK_X = MIN(PKID) FROM ' + @TblName
exec sp_executesql @sql, N'@MinPK_X int OUTPUT', @MinPK_X=@MinPK OUTPUT


Works for me

Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 28. januar 2009 21:30
Til: Access Developers discussion and problem solving
Emne: 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