insert into table using MS sql rocedure and return status message fails in mssql 2008 r2. returns exception procedure is a table valued function

Question

I have the below procedure in sql server 2008 R2 MSSQL procedure

/****** 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_selected ?,?,?,?"); 
            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.

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.

As of now i get the below exception

com.microsoft.sqlserver.jdbc.SQLServerException: The request for procedure 'test_fn_transaction_selected' failed because 'test_fn_transaction_selected' is a table valued function object.

You haven't posted the code for test_fn_transaction_seleted but we can see from the error that it's a TVF. Is your intent to execute the stored procedure test_fn_transaction_insert_log? — Dan Guzman, Jan 31 '15 at 20:34
Your procedure is called test_fn_transaction_insert_log, but you're executing test_fn_transaction_selected — Yuri Zarubin, Jan 31 '15 at 20:41

score 0 · Answer 1 · answered Feb 01 '15 at 08:15

0

I think you need to go through your own code, you comment at header is for a function, you are altering a SP and error message is about a function. Are your sure you are asking right question?

answered Feb 01 '15 at 08:15

UVData

459
2
6
13

insert into table using MS sql rocedure and return status message fails in mssql 2008 r2. returns exception procedure is a table valued function

1 Answers1