troubleshooting Question

Stored procedure: Select Failed: 0 The statement did not return a result set.

Avatar of Mark
Mark asked on
JavaMicrosoft SQL Server 2005
5 Comments3 Solutions169 ViewsLast Modified:
SQL Server 2005

I have the stored procedure shown below. This has been working from within a Java program fine for months and just started giving me the error: "Select Failed: 0 The statement did not return a result set."

It does actually do the insert, however.

When I run this procedure in Server Management Studio as the same user I do get the memberId returned.

Any idea what could be wrong?

ALTER procedure [dbo].[addNewMember_sp] (
  @memberId				varchar(11) = null,
  @memberType			varchar(10),
  @lastName				varchar(20),
  @firstName			varchar(15) = '',
  @middleInit		    char(1) = null,
  @retireeId			varchar(11) = null,
  @relationToRetiree	varchar(3) = null
)

As

declare @newId varchar(11)

if @memberId is null select @newId = convert(varchar,convert(bigint,max(memberId) + 1)) from members
else set @newId = @memberId

insert into members (memberId, memberType, firstName, lastName, middleInit, retireeId, relationToRetiree) 
  values (@newId,@memberType,@firstName,@lastName,@middleInit,@retireeId,@relationToRetiree)

select @newId as memberId

and the java statements:

                query = "exec hprs.dbo.addNewMember_sp @memberId='" + memberId +
                    "',@memberType='ACT',@lastName=" + mkDbString(LastName) +
                    ",@firstName=" + mkDbString(FirstName) +
                    ",@middleInit=" + mkDbString(MiddleInit);

                    try {
                        rs = stmt.executeQuery(query); // leave as executeQuery(), returns memberId
                    }
                    catch ( SQLException sqe) {
                        System.err.println("Select Failed: " + sqe.getErrorCode() + " "
                            + sqe.getMessage() + "\n" + query);
                    }
'
ASKER CERTIFIED SOLUTION
Mark

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros