

Javaでシステム開発をしているのですが、SQLServerを使用しデータを取り出したいです。  
いま問題となっているのはストアドプロシージャでの結果取得についてです。

私自身前任者から引き継いだばかりのプログラム初心者な上SQLのことはわかりませんので、  
本当に申し訳ないですが状況説明すら間違えてる可能性があります。

やりたいことは、  
SQLに名簿があり  
ストアドプロシージャを実行すると条件にあった人物が表示されるというものです。

### 発生している問題・エラーメッセージ

SQLManagementStudio上でストアドプロシージャ実行し、条件にあった検索結果は出て、returnVolumeが0となっています。  
上司はSQLは出来るのですがJavaは出来きず、私が未熟なので結果テーブルが取り出せません。

[![ストアドプロシジャ実行画面](https://i.stack.imgur.com/fpa0b.jpg)](https://i.stack.imgur.com/fpa0b.jpg)

### 該当のソースコード

SQL側

[code]

    USE [Dcat]
    GO
    /****** Object:  StoredProcedure [dbo].[getExamineeRs]    Script Date: 2018/11/22 15:30:16 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:      <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[getExamineeRs] 
        -- Add the parameters for the stored procedure here
        @p_name as nvarchar(50) = null,
        @p_kananame as  nvarchar(50) = null,
        @p_birth as date = null
    
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    
        declare @return int
        set @return = 0
    
        select @p_name = ltrim(rtrim(@p_name))
    
        if (@p_name is null) and (@p_kananame is null) and (@p_birth is null) begin
            --SELECT
            --   [ExamineeId]
            --  ,[ImportGroupId]
            --  ,[ResidentNumber]
            --  ,[ApoDate]
            --  ,[TestDate]
            --  ,[Name]
            --  ,[KanaName]
            --  ,[Gender]
            --  ,[Birth]
            --  ,[Age]
            --  ,[RankId]
            --  ,[LastTestId]
            --  ,[Status]
            --  ,[DeleteFlag]
            --  ,[CreateDate]
            --  ,[CreateUser]
            --  ,[UpdateDate]
            --  ,[UpdateUser]
         -- FROM [dbo].[MExaminee]
    
            set @return = 200
    
        end else begin
            if (@p_name is not null) and (@p_kananame is not null) and (@p_birth is not null) begin     
                select
                   [ExamineeId]
                  ,[ImportGroupId]
                  ,[ResidentNumber]
                  ,[ApoDate]
                  ,[TestDate]
                  ,[Name]       = LTRIM(RTRIM([Name]))
                  ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                  ,[Gender]
                  ,[Birth]
                  ,[Age]
                  ,[RankId]
                  ,[LastTestId]
                  ,[Status]
                  ,[DeleteFlag]
                  ,[CreateDate]
                  ,[CreateUser]
                  ,[UpdateDate]
                  ,[UpdateUser]
                FROM [dbo].[MExaminee]
                where
                    [Name] = @p_name
                and [KanaName] = @p_kananame
                and [Birth] = @p_birth
            end else begin
                if (@p_name is not null) and (@p_kananame is not null) begin
                    select
                       [ExamineeId]
                      ,[ImportGroupId]
                      ,[ResidentNumber]
                      ,[ApoDate]
                      ,[TestDate]
                      ,[Name]       = LTRIM(RTRIM([Name]))
                      ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                      ,[Gender]
                      ,[Birth]
                      ,[Age]
                      ,[RankId]
                      ,[LastTestId]
                      ,[Status]
                      ,[DeleteFlag]
                      ,[CreateDate]
                      ,[CreateUser]
                      ,[UpdateDate]
                      ,[UpdateUser]
                    FROM [dbo].[MExaminee]
                    where
                        [Name] = @p_name
                    and [KanaName] = @p_kananame
                    -- and [Birth] = @p_birth
                end else begin
                    if (@p_name is not null) and (@p_birth is not null) begin
                        select
                           [ExamineeId]
                          ,[ImportGroupId]
                          ,[ResidentNumber]
                          ,[ApoDate]
                          ,[TestDate]
                          ,[Name]       = LTRIM(RTRIM([Name]))
                          ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                          ,[Gender]
                          ,[Birth]
                          ,[Age]
                          ,[RankId]
                          ,[LastTestId]
                          ,[Status]
                          ,[DeleteFlag]
                          ,[CreateDate]
                          ,[CreateUser]
                          ,[UpdateDate]
                          ,[UpdateUser]
                        FROM [dbo].[MExaminee]
                        where
                            [Name] = @p_name
                        -- and [KanaName] = @p_kananame
                        and [Birth] = @p_birth
                    end else begin
                        if (@p_kananame is not null) and (@p_birth is not null) begin
                            select
                               [ExamineeId]
                              ,[ImportGroupId]
                              ,[ResidentNumber]
                              ,[ApoDate]
                              ,[TestDate]
                              ,[Name]       = LTRIM(RTRIM([Name]))
                              ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                              ,[Gender]
                              ,[Birth]
                              ,[Age]
                              ,[RankId]
                              ,[LastTestId]
                              ,[Status]
                              ,[DeleteFlag]
                              ,[CreateDate]
                              ,[CreateUser]
                              ,[UpdateDate]
                              ,[UpdateUser]
                            FROM [dbo].[MExaminee]
                            where
                            --  [Name] = @p_name
                                [KanaName] = @p_kananame
                            and [Birth] = @p_birth
                        end else begin
                            if (@p_name is not null) begin
                                select
                                   [ExamineeId]
                                  ,[ImportGroupId]
                                  ,[ResidentNumber]
                                  ,[ApoDate]
                                  ,[TestDate]
                                  ,[Name]       = LTRIM(RTRIM([Name]))
                                  ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                                  ,[Gender]
                                  ,[Birth]
                                  ,[Age]
                                  ,[RankId]
                                  ,[LastTestId]
                                  ,[Status]
                                  ,[DeleteFlag]
                                  ,[CreateDate]
                                  ,[CreateUser]
                                  ,[UpdateDate]
                                  ,[UpdateUser]
                                FROM [dbo].[MExaminee]
                                where
                                    [Name] = @p_name
                                --  [KanaName] = @p_kananame
                                -- and [Birth] = @p_birth
                            end else begin
                                if (@p_kananame is not null) begin
                                    select
                                       [ExamineeId]
                                      ,[ImportGroupId]
                                      ,[ResidentNumber]
                                      ,[ApoDate]
                                      ,[TestDate]
                                      ,[Name]       = LTRIM(RTRIM([Name]))
                                      ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                                      ,[Gender]
                                      ,[Birth]
                                      ,[Age]
                                      ,[RankId]
                                      ,[LastTestId]
                                      ,[Status]
                                      ,[DeleteFlag]
                                      ,[CreateDate]
                                      ,[CreateUser]
                                      ,[UpdateDate]
                                      ,[UpdateUser]
                                    FROM [dbo].[MExaminee]
                                    where
                                    --  [Name] = @p_name
                                        [KanaName] = @p_kananame
                                    -- and [Birth] = @p_birth
                                end else begin
                                    if (@p_birth is not null) begin
                                        select
                                           [ExamineeId]
                                          ,[ImportGroupId]
                                          ,[ResidentNumber]
                                          ,[ApoDate]
                                          ,[TestDate]
                                          ,[Name]       = LTRIM(RTRIM([Name]))
                                          ,[KanaName]   = LTRIM(RTRIM([KanaName]))
                                          ,[Gender]
                                          ,[Birth]
                                          ,[Age]
                                          ,[RankId]
                                          ,[LastTestId]
                                          ,[Status]
                                          ,[DeleteFlag]
                                          ,[CreateDate]
                                          ,[CreateUser]
                                          ,[UpdateDate]
                                          ,[UpdateUser]
                                        FROM [dbo].[MExaminee]
                                        where
                                        --  [Name] = @p_name
                                        --  [KanaName] = @p_kananame
                                            [Birth] = @p_birth
                                    end
                                end
                            end
                        end
                    end
                end
            end 
        end
    
        return (@return)
    END
    
[/code]

Java側

[code]

            try {
                ResultSet rs = Sql.exec(true, "getExamineeRs", params.name, params.kanaName, params.birth);
                DataTable tbl = new DataTable(rs);
    
    //            DataTable tbl = new DataTable("MExaminee");
                tbl.setLabelMap(
                        "Name", "名前",
                        "KanaName", "フリガナ",
                        "Birth", "生年月日",
                        "Gender", "性別",
                        "ExamineeId", "ID"
                );
                tbl.addHtml(0, "<td><input type=\"button\" value=\"選択\" onclick=\"rowClick(this);\"></td>");
                out.write(tbl.getTable());
    
            } catch (SQLException e) {
                throw new UserRuntimeException(e);
            }
    
[/code]

上司は出来るということなので、  
色々調べてたのですが、returnVolume以外を取り出す方法がわかりませんでした。

  * Java1.8
  * SQLServer2017


CC BY-SA 4.0
2018-11-24T12:28:22.917
50591
2018-11-24T19:11:29.293
2018-11-24T19:11:29.293
19110
31136
question
java sql sql-server
Javaを使いSQLServerのストアドから結果一覧を取得したい