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
Everything we see or seems
Is but a dream within a dream - Edgar Allen Poe