updating multiple rows in MS SQL by way of calling stored procedure takes forever

[Lex]

I have some problem updating multiple records by calling a MS SQL
stored procedure with parameters in java. It seems it locks the records
forever and my application cannot read the table anymore.

Anyone had experience this before?

below is my java code:


CallableStatement cs = null;
try {

// Call a procedure with one IN parameter
cs = con.prepareCall("{call NewUpdateAccounts(?)}");

// Set the value for the IN parameter
cs.setString(1, "1,2,3,4,5,6");

// Execute the stored procedure
cs.executeQuery();

cs.close();
//con.close();

}
catch (SQLException e) {
//sql = sql;
log(e, "ValidateMe.updateAccounts: reading
sp:UpdateAccounts: " + e.getMessage());
}




below is my sp:


ALTER PROC UpdateAccounts
@Active VARCHAR(500)
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(500)


SET @SQLStatement = ' UPDATE Registration SET Active =1 WHERE MemberID
IN(' + @Active + ') '


-- Execute the SQL statement
EXEC(@SQLStatement)

 

[David Portas]

I have some problem updating multiple records by calling a MS SQL
stored procedure with parameters in java. It seems it locks the records
forever and my application cannot read the table anymore.

Anyone had experience this before?

below is my java code:


CallableStatement cs = null;
try {

// Call a procedure with one IN parameter
cs = con.prepareCall("{call NewUpdateAccounts(?)}");

// Set the value for the IN parameter
cs.setString(1, "1,2,3,4,5,6");

// Execute the stored procedure
cs.executeQuery();

cs.close();
//con.close();

}
catch (SQLException e) {
//sql = sql;
log(e, "ValidateMe.updateAccounts: reading
sp:UpdateAccounts: " + e.getMessage());
}




below is my sp:


ALTER PROC UpdateAccounts
@Active VARCHAR(500)
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(500)


SET @SQLStatement = ' UPDATE Registration SET Active =1 WHERE MemberID
IN(' + @Active + ') '


-- Execute the SQL statement
EXEC(@SQLStatement)

Don't use dynamic SQL. For this example I assume that memberid is an
integer. The parameters are optional - just pass 1 or more.

CREATE PROCEDURE UpdateAccounts
(
@memberid1 INTEGER,
@memberid2 INTEGER = NULL,
@memberid3 INTEGER = NULL,
@memberid4 INTEGER = NULL,
@memberid5 INTEGER = NULL,
@memberid6 INTEGER = NULL
/* ... etc */
)

AS

SET NOCOUNT ON

UPDATE registration
SET active =1
WHERE memberid
IN (@memberid1, @memberid2, @memberid3,
@memberid4, @memberid5, @memberid6 /* ... etc */) ;

IF @@ERROR > 0
/* Error handling */

GO

 

[Lex]

Thanks David for your reply.

Actually Im passing dynamically the memberid (it could be one or more)
that is why Im using dynamic SQL.

Is there a disadvantage of using dynamic SQL in terms of performance?

 

[jason]

Are you setting con.setAutoCommit(false) somewhere in the code? It
sounds like the transaction isn't commited, thus it's holding the lock
on the table. Try explicitly issuing a con.commit() after executing
the sproc.