I am trying to write a stored procedure on SQL Server 6.5 that will be called from VFP 5 using SQLExec(). The procedure finds a value that I am looking for, puts it into a variable, updates the table to make that value unavailable to other users (marked as assigned), and returns the value to a VFP cursor.

The procedure works fine execpt that the values from the SELECT statement are not returned to VFP. Any ideas about what I am doing wrong or what I need to do?

I am trying to get the value for @min from the statement: SELECT * FROM mins WHERE m_min=@m_min.

The code for the stored procedures is below:

CREATE PROCEDURE spgetmin AS

DECLARE @m_min varchar(10)
DECLARE @m_assigned varchar(1)
DECLARE @m_recvd_date datetime

DECLARE gmin SCROLL CURSOR
	FOR select * from mins where m_assigned='N' ORDER BY m_min
	FOR UPDATE OF m_assigned
OPEN gmin
FETCH FIRST FROM gmin INTO @m_min, @m_assigned, @m_recvd_date
BEGIN TRANSACTION
	SELECT * FROM mins WHERE m_min=@m_min
	UPDATE mins SET m_assigned='Y' WHERE CURRENT OF gmin
IF @@ERROR <> 0
	BEGIN
	ROLLBACK TRANSACTION
	END
ELSE
	BEGIN
	COMMIT TRANSACTION
	END

DEALLOCATE gmin