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

L

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)
 
D

David Portas

Lex said:
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
 
L

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?
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top