CODEWITHSUNDEEP
javasql-server-2008stored-proceduresjdbc
TechnoCrat
TechnoCrat

Reputation: 2065

Calling MS Sql server stored procedure which has cursor and temporal table in it from java?

I have one stored proc written in MS Sql Server which has cursor and temporal table in it and returns result of one select query at the end.

I am trying to call it from java with following piece of code

final Connection conn = getConnection();
final CallableStatement statement  = conn.prepareCall("{call dbo.storedProcName (?) }");
statement.setString(1, "value1,value2");
final ResultSet rs = statement.executeQuery();
while (rs.next()) {
//some code
}

When I execute this code in java I get the resultset as null. The stored proc returns values when I run it on sql server console with the same parameters I am passing from java code.

Any idea what causing this issue here?

I am using sqljdbc4.jar, Java 7 and SQL Server 2008 R2.

Error stack

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:392)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)

Stored proc rough format

create procedure [dbo].[storedProcName](
    @inpurparam varchar(4000)
)
as
begin
    select some_values into #temp_table where value in (@inputparam)
    //declare some_variables
    declare @table2 table(col1 varchar(10), col2 varchar(10))
    declare cursor for select * from #temp_table
    open cursor
    fetch next from cursor into some_params
    while @@fetch_status = 0
    begin
        //some processing
        //insert into table2 based on some logic
    fetch next ..
 end
 close cursor
 deallocate cursor
 drop table #temp_table
 select col1, col2 from @table2
 order by col1

end go

Upvotes: 3

Views: 2369

Answers (1)

TechnoCrat
TechnoCrat

Reputation: 2065

I was able to fix it with slight change to the stored proc.

create procedure [dbo].[storedProcName](
    @inpurparam varchar(4000)
)
as
begin
**SET NOCOUNT ON**
..
..

I don't know how this fixed the issue but it is working like a charm. I'd appreciate if anybody could throw some light on it.

Upvotes: 1

Related Questions