Exception while executing MS SQL function. The statement did not return a result set.

MSSQL procedure

USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 01/30/2015 11:57:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
                   
alter PROCEDURE test_fn_transaction_insert_log @receipt varchar(25), @amount varchar(25), @stdt varchar(25),
@enddt varchar(25)

AS

SET NOCOUNT ON

INSERT INTO [TransactionLog] ( TimeStamp, CashierID, Total,RecurringStartDate,
RecurringStopDate, CustomerID, ReceiptNumber, Voided, Attendee, ItemLookupCode)
SELECT  CURRENT_TIMESTAMP AS [TimeStamp], tr.CashierID, @amount, @stdt,@enddt,c.ID,tr.ReceiptNumber,
tr.Voided,it.ItemLookupCode, tr.Comment
from "Transaction" tr, TransactionEntry trent, Item it, Customer c
     where trent.TransactionID = tr.ID
     and trent.ItemID = it.ID
     and c.ID=tr.CustomerID
     and tr.ReceiptNumber=@receipt

     
GO

And i am using it in Java as below

Java Dao method

public String saveTr(String a, String b, String c, String d) {
        try
        {
            SqlServerConn conn = new SqlServerConn();
            connect=conn.getConnection();
            String SQL = String.format("Exec test_fn_transaction_insert_log ?,?,?,?");  
            pstmt = connect.prepareStatement(SQL);
            pstmt.setString(1, a);
            pstmt.setString(2, b);
            pstmt.setString(3, c);
            pstmt.setString(4, d);
            rs = pstmt.executeQuery();
        }
        catch (Exception e2)
        {
            e2.printStackTrace();
            return "fail";
        }
        //ArrayList<Transactions> list=getrowFromResultSet2(rs);
       
        return "success";
    }




I get the below exception. Can anyone tell me whats going wrong.
exception
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.



i read the following online about MSSQL which causes a lot of confusion.



a) functions cannot be used for Inserting values into a table other than a temp table.


b) procedures cannot return a table value.



so how can one do the below three
a)send 3 parameters to a function/proc


b) use the input parameters as inputs for a select statement,


c) insert the returned values into a table,


d)return the inserted row / success/fail status.
solarisinfosysAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SimonConnect With a Mentor Commented:
I'm not a Java expert...
 rs = pstmt.executeQuery();
looks like it is expecting a recordset - but the SP performs an INSERT and won't return a recordset, unless you follow the INSERT statement with a SELECT statement (e.g. to get the ID of the newly inserted record)...
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Open in new window


Transaction is a reserved keyword in TSQL, so it is correct to enclose it in double quotes provided that SET QUOTED IDENTIFIER is ON (the default) and as I see you have declared in your SP.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
if Transaction is a table name, apparently it is, remove the " from both sides.

from "Transaction" tr, TransactionEntry trent, Item it, Customer c

from Transaction tr, TransactionEntry trent, Item it, Customer c
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Because you have mentioned:

rs = pstmt.executeQuery();

Open in new window


the code is expecting a result set (i.e. some SELECT statement inside the procedure actually returning data to the caller). Since it does not exist, the "rs" cannot be populated - causing the error.

I'm not a java guy, so I'm not sure if it will work or not, but try replacing
rs = pstmt.executeQuery();

Open in new window

with
pstmt.executeQuery();

Open in new window


Alternatively (this one will work), you can place a SELECT 1 or a SELECT 0 after the INSERT to tell the caller that the procedure executed successfully.

(as an FYI, the rs = pstmt.executeQuery(); should be a valid syntax for a "get" SP, i.e. one that fetches data from the database)
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
to avoid keyword as mentioned above use: from [Transaction] tr, TransactionEntry trent, Item it, Customer c
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.