如何在java/jsp中执行MS SQL Server存储过程，返回表数据?

I am having difficulty executing a MS SQL Server stored procedure from Java/jsp. I wish to return a table set of data; the last line of the stored procedure is a regular select statement from a table.

我在从Java/jsp执行MS SQL服务器存储过程时遇到了困难。我希望返回一组数据;存储过程的最后一行是来自表的常规select语句。

(From this point, executing a stored procedure is a cinch in PHP.)

(从这一点来说，执行存储过程是PHP中的一个小问题。)

I took a look at these sites for help:
www.2netave.com
www.*.com

我浏览了这些网站寻求帮助:www.2netave.com www.*.com

I didn't realize there's a function just for stored procedures, as I was using createStatement() instead.

我没有意识到只有存储过程才有函数，因为我使用了createStatement()。

Now, please understand the stored procedure executes perfectly in SQL Server Management Studio and I have had no problems executing ad-hoc queries in jsp/java with createStatement().

现在，请理解存储过程在SQL Server Management Studio中执行得很好，我使用createStatement()在jsp/java中执行临时查询没有问题。

I created a simple stored procedure that takes no arguments, just to narrow down the problem:

我创建了一个不带参数的简单存储过程，只是为了缩小问题的范围:

CREATE PROCEDURE sp_test AS
BEGIN
PRINT 'HELLO WORLD'
END

Here is the code in my jsp page:

下面是我的jsp页面中的代码:

Class.forName("net.sourceforge.jtds.jdbc.Driver");
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://MySQLServer:1433/test", "user", "pass");
java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}"); 
java.sql.ResultSet ResultSet = cs.execute();

The browser is telling me that the page cannot be displayed because an interal server error has occurred. I know this means there is an issue with the code above.

浏览器告诉我页面无法显示，因为发生了内部服务器错误。我知道这意味着上面的代码有问题。

I tried this:

我试着这样的:

java.sql.ResultSet ResultSet = cs.executeQuery();

And this:

这:

java.sql.CallableStatement cs = conn.prepareCall("{execute sp_test}");

And this:

这:

java.sql.CallableStatement cs = conn.prepareCall("{exec sp_test}");

And nothing worked. Once I can get this working, then I can run an actual stored procedure that returns table data from a select statement. But I can't even get this dummy stored procedure to work.

和毫无效果。一旦我可以运行这个过程，那么我就可以运行一个实际的存储过程，它从select语句返回表数据。但是我甚至不能让这个虚拟存储过程工作。

What am I doing wrong here?

我在这里做错了什么?

Thank you.

谢谢你！

Update:

更新:

Checked the server logs (IIS) and my HTTP proxy, fiddler, and it doesn't report anything. However, the IIS is using tomcat as the servlet engine for jsp pages. And tomcat log file reported the following:

检查了服务器日志(IIS)和我的HTTP代理fiddler，它没有报告任何内容。但是，IIS使用tomcat作为jsp页面的servlet引擎。tomcat日志文件报告如下:

An error occurred at line: 20 in the jsp file: /test.jsp
Type mismatch: cannot convert from boolean to ResultSet
17: 
18:     java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}");
19:     
20:     java.sql.ResultSet ResultSet = cs.execute();
21: 
22: //  java.sql.ResultSet ResultSet = state.executeQuery(SQL); 
23: 

I tried changing the above to:

我试着把上面的改为:

cs.execute();

And the log files reported:

日志文件报告:

- Servlet.service() for servlet jsp threw exception
java.sql.SQLException: The EXECUTE permission was denied on the object 'sp_test', database 'test', schema 'dbo'.

So, I have figured out I have to GRANT EXECUTE to the user. The other issue is returning table data from a stored procedure.

所以，我想我必须给用户执行。另一个问题是从存储过程返回表数据。

If I have a procedure like this:

如果我有这样的程序:

CREATE PROCEDURE sp_test2 AS
BEGIN
SELECT * FROM TABLE
END

How do I manipulate the table data in jsp? Would ResultSet work or is that only for ad-hoc queries, as opposed to stored procedures, where one would use createStatement() to execute a query?

如何在jsp中操作表数据?ResultSet起作用了吗?还是只针对临时查询，而不是存储过程，在存储过程中，使用createStatement()来执行查询?

Thank you.

谢谢你！

Update2:

更新2:

Solution:

解决方案:

In order to manipulate table data, I had to use this:

为了操作表数据，我必须这样做:

java.sql.ResultSet RS = cs.executeQuery();

It failed on execute() and it failed on naming the ResultSet object "ResultSet". It never complained about this in the past with createStatement(). But for some reason, with stored procedures, it didn't like this naming convention.

它在execute()上失败，在命名ResultSet对象“ResultSet”上失败。在过去，它从来没有抱怨过createStatement()。但是由于某种原因，对于存储过程，它不喜欢这种命名约定。

Thank you.

谢谢你！

4 个解决方案

#1

Our server calls stored procs from Java like so - works on both SQL Server 2000 & 2008:

我们的服务器像这样从Java调用存储的proc -在SQL server 2000和2008上都可以工作:

String SPsql = "EXEC <sp_name> ?,?";   // for stored proc taking 2 parameters
Connection con = SmartPoolFactory.getConnection();   // java.sql.Connection
PreparedStatement ps = con.prepareStatement(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(<timeout value>);
ps.setString(1, <param1>);
ps.setString(2, <param2>);
ResultSet rs = ps.executeQuery();

#2

Thank to Brian for the code. I was trying to connect to the sql server with {call spname(?,?)} and I got errors, but when I change my code to exec sp... it works very well.

谢谢布莱恩的密码。我试图用{call spname(?，?)}连接到sql服务器，但我有错误，但当我将代码更改为exec sp…它工作得很好。

I post my code in hope this helps others with problems like mine:

我张贴我的代码希望这能帮助其他人解决像我这样的问题:

ResultSet rs = null;
PreparedStatement cs=null;
Connection conn=getJNDIConnection();

try {
    cs=conn.prepareStatement("exec sp_name ?,?,?,?,?,?,?");
    cs.setEscapeProcessing(true);
    cs.setQueryTimeout(90);

    cs.setString(1, "valueA");

    cs.setString(2, "valueB");

    cs.setString(3, "0418");

    //commented, because no need to register parameters out!, I got results from the resultset. 
    //cs.registerOutParameter(1, Types.VARCHAR);
    //cs.registerOutParameter(2, Types.VARCHAR);

    rs = cs.executeQuery();
    ArrayList<ObjectX> listaObjectX = new ArrayList<ObjectX>();
    while (rs.next()) {

        ObjectX to = new ObjectX();
        to.setFecha(rs.getString(1));
        to.setRefId(rs.getString(2));
        to.setRefNombre(rs.getString(3));
        to.setUrl(rs.getString(4));

        listaObjectX.add(to);

    }
    return listaObjectX;
     } catch (SQLException se) {
        System.out.println("Error al ejecutar SQL"+ se.getMessage());
        se.printStackTrace();
        throw new IllegalArgumentException("Error al ejecutar SQL: " + se.getMessage());

    } finally {

        try {

            rs.close();
            cs.close();
          con.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

#3

Frequently we deal with other fellow java programmers work which create these Stored Procedure. and we do not want to mess around with it. but there is possibility you get the result set where these exec sample return 0 (almost Stored procedure call returning zero).

我们经常与创建这些存储过程的其他java程序员打交道。我们不想把它搞得一团糟。但是您可能会得到这些exec示例返回0的结果集(几乎是存储过程调用返回0)。

check this sample :

检查这个示例:

public void generateINOUT(String USER, int DPTID){

    try {


        conUrl = JdbcUrls + dbServers +";databaseName="+ dbSrcNames+";instance=MSSQLSERVER";

        con = DriverManager.getConnection(conUrl,dbUserNames,dbPasswords);
        //stat = con.createStatement();
        con.setAutoCommit(false); 
        Statement st = con.createStatement(); 

        st.executeUpdate("DECLARE @RC int\n" +
                "DECLARE @pUserID nvarchar(50)\n" +
                "DECLARE @pDepartmentID int\n" +
                "DECLARE @pStartDateTime datetime\n" +
                "DECLARE @pEndDateTime datetime\n" +
                "EXECUTE [AccessManager].[dbo].[SP_GenerateInOutDetailReportSimple] \n" +
                ""+USER +
                "," +DPTID+
                ",'"+STARTDATE +
                "','"+ENDDATE+"'");

        ResultSet rs = st.getGeneratedKeys();

        while (rs.next()){
              String userID = rs.getString("UserID");
              Timestamp timeIN = rs.getTimestamp("timeIN");
              Timestamp timeOUT = rs.getTimestamp ("timeOUT");
              int totTime = rs.getInt ("totalTime");
              int pivot = rs.getInt ("pivotvalue");

              timeINS = sdz.format(timeIN);
              userIN.add(timeINS);

              timeOUTS = sdz.format(timeOUT);
              userOUT.add(timeOUTS);

              System.out.println("User : "+userID+" |IN : "+timeIN+" |OUT : "+timeOUT+"| Total Time : "+totTime+" | PivotValue : "+pivot);
        }

        con.commit();

    }catch (Exception e) {
        e.printStackTrace();
        System.out.println(e);
        if (e.getCause() != null) {
        e.getCause().printStackTrace();}
    }  
}

I came to this solutions after few days trial and error, googling and get confused ;) it execute below Stored Procedure :

经过几天的反复试验，我终于找到了这个解决方案，我在谷歌上搜索并感到困惑。

USE [AccessManager]
GO
/****** Object:  StoredProcedure [dbo].[SP_GenerateInOutDetailReportSimple]    
Script Date: 04/05/2013 15:54:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SP_GenerateInOutDetailReportSimple]
(
@pUserID nvarchar(50),
@pDepartmentID int,
@pStartDateTime datetime,
@pEndDateTime datetime
)
AS


Declare @ErrorCode int
Select @ErrorCode = @@Error

Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
    Select @TransactionCountOnEntry = @@TranCount
    BEGIN TRANSACTION
End


If @ErrorCode = 0
Begin       

    -- Create table variable instead of SQL temp table because report wont pick up the temp table
    DECLARE @tempInOutDetailReport TABLE
    (
        UserID nvarchar(50),
        LogDate datetime,   
        LogDay varchar(20), 
        TimeIN datetime,
        TimeOUT datetime,
        TotalTime int,
        RemarkTimeIn nvarchar(100),
        RemarkTimeOut nvarchar(100),
        TerminalIPTimeIn varchar(50),
        TerminalIPTimeOut varchar(50),
        TerminalSNTimeIn nvarchar(50),
        TerminalSNTimeOut nvarchar(50),
        PivotValue int
    )           

    -- Declare variables for the while loop
    Declare @LogUserID nvarchar(50)
    Declare @LogEventID nvarchar(50)
    Declare @LogTerminalSN nvarchar(50)
    Declare @LogTerminalIP nvarchar(50)
    Declare @LogRemark nvarchar(50)
    Declare @LogTimestamp datetime  
    Declare @LogDay nvarchar(20)

    -- Filter off userID, departmentID, StartDate and EndDate if specified, only process the remaining logs
    -- Note: order by user then timestamp
    Declare LogCursor Cursor For 
    Select distinct access_event_logs.USERID, access_event_logs.EVENTID, 
        access_event_logs.TERMINALSN, access_event_logs.TERMINALIP,
        access_event_logs.REMARKS, access_event_logs.LOCALTIMESTAMP, Datename(dw,access_event_logs.LOCALTIMESTAMP) AS WkDay
    From access_event_logs
        Left Join access_user on access_user.User_ID = access_event_logs.USERID
        Left Join access_user_dept on access_user.User_ID = access_user_dept.User_ID
    Where ((Dept_ID = @pDepartmentID) OR (@pDepartmentID IS NULL))
        And ((access_event_logs.USERID LIKE '%' + @pUserID + '%') OR (@pUserID IS NULL)) 
        And ((access_event_logs.LOCALTIMESTAMP >= @pStartDateTime ) OR (@pStartDateTime IS NULL)) 
        And ((access_event_logs.LOCALTIMESTAMP < DATEADD(day, 1, @pEndDateTime) ) OR (@pEndDateTime IS NULL)) 
        And (access_event_logs.USERID != 'UNKNOWN USER') -- Ignore UNKNOWN USER
    Order by access_event_logs.USERID, access_event_logs.LOCALTIMESTAMP

    Open LogCursor

    Fetch Next 
    From LogCursor
    Into @LogUserID, @LogEventID, @LogTerminalSN, @LogTerminalIP, @LogRemark, @LogTimestamp, @LogDay

    -- Temp storage for IN event details
    Declare @InEventUserID nvarchar(50)
    Declare @InEventDay nvarchar(20)
    Declare @InEventTimestamp datetime
    Declare @InEventRemark nvarchar(100)
    Declare @InEventTerminalIP nvarchar(50)
    Declare @InEventTerminalSN nvarchar(50)

    -- Temp storage for OUT event details
    Declare @OutEventUserID nvarchar(50)        
    Declare @OutEventTimestamp datetime
    Declare @OutEventRemark nvarchar(100)
    Declare @OutEventTerminalIP nvarchar(50)
    Declare @OutEventTerminalSN nvarchar(50)

    Declare @CurrentUser varchar(50) -- used to indicate when we change user group
    Declare @CurrentDay varchar(50) -- used to indicate when we change day
    Declare @FirstEvent int -- indicate the first event we received     
    Declare @ReceiveInEvent int -- indicate we have received an IN event
    Declare @PivotValue int -- everytime we change user or day - we reset it (reporting purpose), if same user..keep increment its value
    Declare @CurrTrigger varchar(50) -- used to keep track of the event of the current event log trigger it is handling 
    Declare @CurrTotalHours int -- used to keep track of total hours of the day of the user

    Declare @FirstInEvent datetime
    Declare @FirstInRemark nvarchar(100)
    Declare @FirstInTerminalIP nvarchar(50)
    Declare @FirstInTerminalSN nvarchar(50)
    Declare @FirstRecord int -- indicate another day of same user

    Set @PivotValue = 0 -- initialised
    Set @CurrentUser = '' -- initialised
    Set @FirstEvent = 1 -- initialised
    Set @ReceiveInEvent = 0 -- initialised  
    Set @CurrTrigger = '' -- Initialised    
    Set @CurrTotalHours = 0 -- initialised
    Set @FirstRecord = 1 -- initialised
    Set @CurrentDay = '' -- initialised

    While @@FETCH_STATUS = 0
    Begin
        -- use to track current log trigger
        Set @CurrTrigger =LOWER(@LogEventID)

        If (@CurrentUser != '' And @CurrentUser != @LogUserID) -- new batch of user
        Begin
            If @ReceiveInEvent = 1  -- previous IN event is not cleared (no OUT is found)
            Begin
                -- Check day
                If (@CurrentDay != @InEventDay) -- change to another day                    
                    Set @PivotValue = 0 -- Reset                        

                Else -- same day
                    Set @PivotValue = @PivotValue + 1 -- increment
                Set @CurrentDay = @InEventDay -- update the day

                -- invalid row (only has IN event)
                Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, RemarkTimeIn, TerminalIPTimeIn, 
                    TerminalSNTimeIn, PivotValue, LogDate )
                values( @InEventUserID, @InEventDay, @InEventTimestamp, @InEventRemark, @InEventTerminalIP, 
                    @InEventTerminalSN, @PivotValue, DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))                                                         
            End                 

            Set @FirstEvent = 1 -- Reset flag (we are having a new user group)
            Set @ReceiveInEvent = 0 -- Reset
            Set @PivotValue = 0 -- Reset
            --Set @CurrentDay = '' -- Reset
        End

        If LOWER(@LogEventID) = 'in' -- IN event
        Begin           
            If @ReceiveInEvent = 1  -- previous IN event is not cleared (no OUT is found)
            Begin
                -- Check day
                If (@CurrentDay != @InEventDay) -- change to another day
                Begin
                    Set @PivotValue = 0 -- Reset

                    --Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, TimeOUT, TotalTime, RemarkTimeIn,
                    --  RemarkTimeOut, TerminalIPTimeIn, TerminalIPTimeOut, TerminalSNTimeIn, TerminalSNTimeOut, PivotValue,
                    --  LogDate)
                    --values( @LogUserID, @CurrentDay, @FirstInEvent, @LogTimestamp,  @CurrTotalHours,
                    --  @FirstInRemark, @LogRemark, @FirstInTerminalIP, @LogTerminalIP, @FirstInTerminalSN, @LogTerminalSN, @PivotValue,
                    --  DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))  
                End
                Else
                    Set @PivotValue = @PivotValue + 1 -- increment
                Set @CurrentDay = @InEventDay -- update the day


                -- invalid row (only has IN event)
                Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, RemarkTimeIn, TerminalIPTimeIn, 
                    TerminalSNTimeIn, PivotValue, LogDate )
                values( @InEventUserID, @InEventDay, @InEventTimestamp, @InEventRemark, @InEventTerminalIP, 
                    @InEventTerminalSN, @PivotValue, DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))                     
            End         

            If((@CurrentDay != @LogDay And @CurrentDay != '') Or (@CurrentUser != @LogUserID And @CurrentUser != '') )
            Begin

                    Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, TimeOUT, TotalTime, RemarkTimeIn,
                        RemarkTimeOut, TerminalIPTimeIn, TerminalIPTimeOut, TerminalSNTimeIn, TerminalSNTimeOut, PivotValue,
                        LogDate)
                    values( @CurrentUser, @CurrentDay, @FirstInEvent, @OutEventTimestamp, @CurrTotalHours,
                        @FirstInRemark, @OutEventRemark, @FirstInTerminalIP, @OutEventTerminalIP, @FirstInTerminalSN, @LogTerminalSN, @PivotValue,
                        DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))      

                Set @FirstRecord = 1
            End
            -- Save it
            Set @InEventUserID = @LogUserID                         
            Set @InEventDay = @LogDay
            Set @InEventTimestamp = @LogTimeStamp
            Set @InEventRemark = @LogRemark
            Set @InEventTerminalIP = @LogTerminalIP
            Set @InEventTerminalSN = @LogTerminalSN

            If (@FirstRecord = 1) -- save for first in event record of the day
            Begin
                Set @FirstInEvent = @LogTimestamp
                Set @FirstInRemark = @LogRemark
                Set @FirstInTerminalIP = @LogTerminalIP
                Set @FirstInTerminalSN = @LogTerminalSN
                Set @CurrTotalHours = 0 --initialise total hours for another day
            End

            Set @FirstRecord = 0 -- no more first record of the day
            Set @ReceiveInEvent = 1 -- indicate we have received an "IN" event
            Set @FirstEvent = 0  -- no more "first" event
        End
        Else If LOWER(@LogEventID) = 'out' -- OUT event
        Begin
            If @FirstEvent = 1 -- the first OUT record when change users 
            Begin
                -- Check day
                If (@CurrentDay != @LogDay) -- change to another day
                    Set @PivotValue = 0 -- Reset
                Else
                    Set @PivotValue = @PivotValue + 1 -- increment
                Set @CurrentDay = @LogDay -- update the day

                -- Only an OUT event (no IN event) - invalid record but we show it anyway
                Insert into @tempInOutDetailReport( UserID, LogDay, TimeOUT, RemarkTimeOut, TerminalIPTimeOut, TerminalSNTimeOut,
                    PivotValue, LogDate )
                values( @LogUserID, @LogDay, @LogTimestamp, @LogRemark, @LogTerminalIP, @LogTerminalSN, @PivotValue,
                    DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @LogTimestamp)))                                              

                Set @FirstEvent = 0 -- not "first" anymore
            End
            Else -- Not first event
            Begin       

                If @ReceiveInEvent = 1 -- if there are IN event previously
                Begin           
                    -- Check day
                    If (@CurrentDay != @InEventDay) -- change to another day                        
                        Set @PivotValue = 0 -- Reset                        
                    Else
                        Set @PivotValue = @PivotValue + 1 -- increment
                    Set @CurrentDay = @InEventDay -- update the day     
                    Set @CurrTotalHours = @CurrTotalHours +  DATEDIFF(second,@InEventTimestamp, @LogTimeStamp) -- update total time             

                    Set @OutEventRemark = @LogRemark
                    Set @OutEventTerminalIP = @LogTerminalIP
                    Set @OutEventTerminalSN = @LogTerminalSN
                    Set @OutEventTimestamp = @LogTimestamp          
                    -- valid row
                    --Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, TimeOUT, TotalTime, RemarkTimeIn,
                    --  RemarkTimeOut, TerminalIPTimeIn, TerminalIPTimeOut, TerminalSNTimeIn, TerminalSNTimeOut, PivotValue,
                    --  LogDate)
                    --values( @LogUserID, @InEventDay, @InEventTimestamp, @LogTimestamp,  Datediff(second, @InEventTimestamp, @LogTimeStamp),
                    --  @InEventRemark, @LogRemark, @InEventTerminalIP, @LogTerminalIP, @InEventTerminalSN, @LogTerminalSN, @PivotValue,
                    --  DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))                      

                    Set @ReceiveInEvent = 0 -- Reset
                End
                Else -- no IN event previously
                Begin
                    -- Check day
                    If (@CurrentDay != @LogDay) -- change to another day
                        Set @PivotValue = 0 -- Reset
                    Else
                        Set @PivotValue = @PivotValue + 1 -- increment
                    Set @CurrentDay = @LogDay -- update the day                         

                    -- invalid row (only has OUT event)
                    Insert into @tempInOutDetailReport( UserID, LogDay, TimeOUT, RemarkTimeOut, TerminalIPTimeOut, TerminalSNTimeOut,
                        PivotValue, LogDate )
                    values( @LogUserID, @LogDay, @LogTimestamp, @LogRemark, @LogTerminalIP, @LogTerminalSN, @PivotValue,
                        DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @LogTimestamp)) )             
                End             
            End         
        End

        Set @CurrentUser = @LogUserID -- update user        

        Fetch Next 
        From LogCursor
        Into @LogUserID, @LogEventID, @LogTerminalSN, @LogTerminalIP, @LogRemark, @LogTimestamp, @LogDay
    End 

    -- Need to handle the last log if its IN log as it will not be processed by the while loop
    if @CurrTrigger='in'
    Begin 
    -- Check day
            If (@CurrentDay != @InEventDay) -- change to another day
                Set @PivotValue = 0 -- Reset
            Else -- same day
                Set @PivotValue = @PivotValue + 1 -- increment
            Set @CurrentDay = @InEventDay -- update the day

            -- invalid row (only has IN event)
            Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, RemarkTimeIn, TerminalIPTimeIn, 
                TerminalSNTimeIn, PivotValue, LogDate )
            values( @InEventUserID, @InEventDay, @InEventTimestamp, @InEventRemark, @InEventTerminalIP, 
                    @InEventTerminalSN, @PivotValue, DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))     
    End
    else if @CurrTrigger = 'out'
    Begin
        Insert into @tempInOutDetailReport( UserID, LogDay, TimeIN, TimeOUT, TotalTime, RemarkTimeIn,
        RemarkTimeOut, TerminalIPTimeIn, TerminalIPTimeOut, TerminalSNTimeIn, TerminalSNTimeOut, PivotValue,
        LogDate)
        values( @LogUserID, @CurrentDay, @FirstInEvent, @LogTimestamp,  @CurrTotalHours,
        @FirstInRemark, @LogRemark, @FirstInTerminalIP, @LogTerminalIP, @FirstInTerminalSN, @LogTerminalSN, @PivotValue,
        DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @InEventTimestamp)))  
    End

    Close LogCursor
    Deallocate LogCursor

    Select * 
    From @tempInOutDetailReport tempTable
        Left Join access_user on access_user.User_ID = tempTable.UserID
    Order By tempTable.UserID, LogDate


End



If @@TranCount > @TransactionCountOnEntry
Begin
     If @ErrorCode = 0
            COMMIT TRANSACTION
     Else
            ROLLBACK TRANSACTION
End

return @ErrorCode

you will get the "java SQL Code" by right click on stored procedure in your database. something like this :

通过右键单击数据库中的存储过程，您将获得“java SQL代码”。是这样的:

DECLARE @RC int
DECLARE @pUserID nvarchar(50)
DECLARE @pDepartmentID int
DECLARE @pStartDateTime datetime
DECLARE @pEndDateTime datetime

-- TODO: Set parameter values here.

EXECUTE @RC = [AccessManager].[dbo].[SP_GenerateInOutDetailReportSimple] 
@pUserID,@pDepartmentID,@pStartDateTime,@pEndDateTime
GO

check the query String I've done, that is your homework ;) so sorry answering this long, this is my first answer since I register few weeks ago to get answer.

请检查我所做的查询字符串，这是您的作业。)抱歉回答这么长，这是我几周前注册后的第一个答案。

#4

FWIW, sp_test will not be returning anything but an integer (all SQL Server stored procs just return an integer) and no result sets on the wire (since no SELECT statements). To get the output of the PRINT statements, you normally use the InfoMessage event on the connection (not the command) in ADO.NET.

sp_test只返回一个整数(所有SQL服务器存储的procs都只返回一个整数)，并且在连接上没有结果集(因为没有SELECT语句)。要获取打印语句的输出，您通常在ADO.NET的连接(而不是命令)上使用InfoMessage事件。