Sql-server – Retrieving data from stored procedure’s cursor

cursorsjavasql serversql-server-2008stored-procedures

I want to use the ResultSet in java program to get data from Stored procedure's cursor. Is that possible with SQL Server?

My code:

CREATE PROCEDURE dbo.SampleProcedure AS 
DECLARE @FirstName varchar(64)
DECLARE @LastName varchar(64)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE

OPEN c1

FETCH NEXT FROM c1
INTO @FirstName, @LastName


CLOSE c1
DEALLOCATE c1

When I use EXEC SampleProcedure, I just got the output as "Command(s) completed successfully" and the Resultset shows null in java program. So what to change in my code so that my ResultSet retrieves data?

I want to know how to prepare ResultSet in the SQL Server code so that I can retrieve the same in java program.

Best Answer

SQL Server stored procedures can return record-sets from a query, a CURSOR would not be required (unlike in Oracle). The query below is all that's needed to get a recordset:

CREATE PROCEDURE dbo.SampleProcedure 
AS 
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE
Related Question