We are using stored procedure for data retrieval according to the parameter passed into it.
The issue is that SP returns data 1st time for the given parameter but not for 2nd time.
Following have been done to debug the issue:
Assigned Select statement in a variable and inserted into temp table. Same SQL statement inserted into temp table and returned same record on execution
PS: @@Fetch_Status = 0 on 1st calling of SP and 1 on 2nd call!
Because of this SP didnt return expected values 2nd time
Any help would be highly appreciated.
Create Procedure GetEmployeeName
Declare @LastName varchar (100), @FirstName varchar(100),
@Designaation varchar (100)
Declare MyCursor Cursor FOR SELECT LastName, FirstName, Designaation FROM Employee WHERE [JoinDate]= @JoinDate
FETCH NEXT FROM MyCursor INTO @LastName, @FirstName, @Designaation
WHILE @@Fetch_Status = 0
IF @Designaation = UPPER(Manager)
EXEC CalculateSalary(1) -- A cursor is opened and closed accordingly
ELSE IF @Designaation = UPPER(Admin)
EXEC CalculateSalary(2) -- A cursor is opened and closed accordingly
FETCH NEXT FROM MyCursor INTO @LastName, @FirstName