October 14, 2003 at 3:35 pm
I am trying to create a stored procedure to update a record in the database. However Im not allowed to use UPDATE. I guess im supposed to delete the record selected and re-insert new entries into it. Check out what i have. Keep in mind that it currently uses UPDATE. I just want to know what to replace that with and if i am using the transaction properly. . . thanks in advance (as always)
CREATE PROCEDURE spChangeStudent
(
@ID tinyint,
@LASTNAME varchar(20),
@FIRSTNAME varchar(20),
@DEPT char(2),
@Balance money,
@ADDRESS char(10)
)
AS
SET NOCOUNT OFF;
BEGIN TRANSACTION
SELECT * FROM tblStudent WHERE fldStudentID = @ID
-- BELOW IS WHAT NEEDS TO BE CHANGED --
UPDATE tblStudent SET fldStudentID = @ID, fldLastName = @LASTNAME, fldFirstName = @FIRSTNAME, fldDepartmentCode = @DEPT,
fldBalance = @Balance, fldAddress = @ADDRESS
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the information'
ROLLBACK
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new information has been loaded'
COMMIT
RETURN(0)
END
GO
October 14, 2003 at 3:38 pm
i now have this... Question now is,
Should I use something to check the transaction again? Like after i delete the student, make sure that it did it okay?
thanks
CREATE PROCEDURE spChangeStudent
(
@ID tinyint,
@LASTNAME varchar(20),
@FIRSTNAME varchar(20),
@DEPT char(2),
@Balance money,
@ADDRESS char(10)
)
AS
SET NOCOUNT OFF;
BEGIN TRANSACTION
DELETE FROM tblStudent WHERE fldStudentID = @ID
INSERT INTO tblStudent(fldStudentID, fldLastName, fldFirstName, fldDepartmentCode, fldBalance, fldAddress)
VALUES (@ID, @LASTNAME, @FIRSTNAME, @DEPT, @Balance, @ADDRESS)
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the information'
ROLLBACK
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new information has been loaded'
COMMIT
RETURN(0)
END
GO
October 14, 2003 at 9:19 pm
Think you want to use the update construct but do not include the ID field in the update list and then use the "where fldStudentID = @ID" clause to hit only the single record you want to hit.
Arden
October 15, 2003 at 3:22 am
You should check @@ERROR status after you delete to make sure that the operation was successful.
October 15, 2003 at 8:29 am
Do the input parameters to the stored procedure cover all fields in the table? If not, you would need to use the following snippet to replace the UPDATE statement:
-- Assumes a transaction isolation level of read commited
BEGIN TRAN
SELECT @oldField1 = Field1, @oldField2 = Field2, ...
FROM MyTable with (rowlock, updlock)
WHERE pkID = @pkID
IF @@ROWCOUNT = 0 BEGIN
ROLLBACK TRAN
RETURN
END
DELETE MyTable WHERE pkID = @pkID
IF @@ERROR <> 0 or @@ROWCOUNT = 0 BEGIN
ROLLBACK TRAN
RAISERROR('error message', 16, 1)
RETURN
INSERT MyTable(field1, field2, ...)
VALUES (coalesce(@field1Param, @oldField1), @oldField2, ...)
IF @@ERROR <> 0 or @@ROWCOUNT = 0 BEGIN
ROLLBACK TRAN
RAISERROR('error message', 16, 1)
RETURN
END
COMMIT TRAN
Using coalesce will allow you to pass NULL parameters to the stored procedure indicating that the field is not to be updated but rather to use the existing field value.
Hope this helps.
--Buddy
Edited by - buddy__a on 10/15/2003 08:29:45 AM
October 16, 2003 at 6:59 pm
Buddy,
Since you don't have "BEGIN" and "END" on your error block he will never get to the second query! 🙂
mcmcom Give this a try....
CREATE PROCEDURE spChangeStudent
(
@ID tinyint,
@LASTNAME varchar(20),
@FIRSTNAME varchar(20),
@DEPT char(2),
@BALANCE money,
@ADDRESS char(10)
)
AS
DECLARE @iErr int
SET NOCOUNT OFF
BEGIN TRANSACTION
DELETE FROM tblStudent WHERE fldStudentID = @ID
-- Test the error value.
SELECT @iErr = @@ERROR
IF @iErr != 0
BEGIN
RAISERROR('Error deleting student',16,1)
ROLLBACK TRAN
RETURN 99
END
INSERT INTO tblStudent(fldStudentID, fldLastName, fldFirstName, fldDepartmentCode, fldBalance, fldAddress)
VALUES (@ID, @LASTNAME, @FIRSTNAME, @DEPT, @BALANCE, @ADDRESS)
-- Test the error value.
SELECT @iErr = @@ERROR
IF @iErr != 0
BEGIN
RAISERROR('Error adding student',16,1)
ROLLBACK TRAN
RETURN 99
END
-- Return 0 to the calling program to indicate success.
PRINT 'The new information has been loaded'
COMMIT
RETURN 0
END
GO
Also, note that the @@ERROR variable gets reset on every line so you want to set it to a local variable incase you want to use it later. Same thing for @@ROWCOUNT AND @@FETCH_STATUS.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 12, 2003 at 5:07 am
Use something like this once u have deleted the row...
and before inserting the new ones...
--If there was an error rollback the tran and quit(add other error handling if necessary)
IF @@Error <> 0
Begin
rollback tran
Return
END
Cheers,
Arvind
Arvind
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply