December 7, 2007 at 2:09 pm
I am trying to update multiple rows using stored procedure. here is code sample, cna someone help me please.
CREATE PROCEDURE Myprocedure
@newplayerID NVARCHAR(100) = NULL,
@newballID NVARCHAR(100) = NULL
@oldplayerID NVARCHAR(100) = NULL,
@oldballID NVARCHAR(100) = NULL
@newplayerName NVARCHAR(100) = NULL,
@newballName NVARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON
UPDATE Mytablename
SET
dbPlayerID = @newplayerID, dbBallID = @newballID, dbPlayerName = @newplayerName, dbBallName = @newballName
WHERE
dbPlayerID = @oldplayerID, dbBallID = @oldballID
END
GO
NOTE: Please notice that old player and ball ids will be passed to this procedure from webpage, and new player and ball ids will be selected from web page, both are bound to database. This stored procedure generate exception that "subquery returns more than one row". Yes I know my criteria return more than one rows and i want updations in all those rows which comes under my WHERE clause. Whats problem with MS SQL to update them with same value.
Early reply would be appreciated.
Regards
--JK
December 7, 2007 at 3:45 pm
Not sure if this will help, but check the where statement
CREATE PROCEDURE Myprocedure
@newplayerID NVARCHAR(100) = NULL,
@newballID NVARCHAR(100) = NULL
@oldplayerID NVARCHAR(100) = NULL,
@oldballID NVARCHAR(100) = NULL
@newplayerName NVARCHAR(100) = NULL,
@newballName NVARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON
UPDATE Mytablename
SET
dbPlayerID = @newplayerID, dbBallID = @newballID, dbPlayerName = @newplayerName, dbBallName = @newballName
WHERE
dbPlayerID = @oldplayerID AND dbBallID = @oldballID
END
GO
December 7, 2007 at 6:24 pm
I am sorry, that was my typing mistake, I am already using AND also I missed 2 qomas.
Also I found that this way to perform update is CORRECT, problem is I was calling a Trigger right after this stored procedure executed, that Trigger works fine if I run it alone, but when I run it right after this stored procedure I get error, So Now I am working on that to find reason.
Thanks for you reply.
Regards
--JK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply