MSSQL 2005 - JDBC4 and subqueries

Question

I tried a query which

A) fills var table
B) gets the var table data as helpful to select next data

CREATE PROCEDURE  Test
AS
BEGIN
DECLARE @A TABLE
(
id INT NOT NULL,
name VARCHAR(50)

);

INSERT @A SELECT id,name FROM table1 WHERE id>10

DECLARE @B TABLE
(

  address VARCHAR(255),
  city VARCHAR(128)

);

INSERT @b SELECT address,city FROM table2
WHERE id IN(SELECT id FROM @A) 
END;

... so, as a result, I have two select statements in my procedure :S The thing is... All procedures which contain just one select statement behave fine with JDBC4 but here something is wrong because when procedure contains two select statement it returns nothing :( So my question can two select statement cause the problem with jdbc4? A if it does how to fix it?

score 3 · Accepted Answer · edited May 23 '17 at 10:08

3

Try adding SET NOCOUNT ON (edit: once at the top of the procedure body) to the stored procedure. The result of this is sent back and may be confusing JDBC 4: this is quite common...

See SET NOCOUNT ON usage for more

CREATE PROCEDURE  Test
AS
BEGIN
SET NOCOUNT ON; -- here

DECLARE @A TABLE
...

edited May 23 '17 at 10:08

Community

1
1

answered Dec 13 '11 at 18:22

gbn

422,506
82
585
676

Oh, I am not pretty sure... where should I add it? to block the first select statement? – user592704 Dec 13 '11 at 18:24
Thank you it works. But it makes query more slow to get result? – user592704 Dec 13 '11 at 18:37

MSSQL 2005 - JDBC4 and subqueries

1 Answers1