SQL Stored Procedure - From Insert to Update
-
Hello,
I am new to SQL Server 2005 and stored procedures. I would like to convert the following Stored Proc from an "Add" procedure to an "Update" procedure. Any help is appreciated!!!
Thanks,
Larry
USE
[DNN]
GO
/****** Object: StoredProcedure [dbo].[UpdatePollResult] Script Date: 08/10/2007 08:57:34 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
/** Create Stored Procedure **/
ALTER
PROCEDURE [dbo].[UpdatePollResult]
@PollOptionID
int,
int,
@CustomerID
varchar(15),
@Rating
int,
@Weight
int,
@Comment
as ntext,
@IsAnon
as bit
AS
DECLARE
@i int
DECLARE
@PollResultID int
SELECT
@i = COUNT(*) FROM PollResults
WHERE
(UserID = @user-id) AND (CustomerID = @CustomerID) AND (PollOptionID = @PollOptionID)
IF
(@i = 0)
BEGIN
INSERT
INTO PollResults (PollOptionID, UserID, CustomerID, Rating, Weight)
VALUES
(@PollOptionID, @user-id, @CustomerID, @Rating, @Weight)
SELECT
@PollResultID = @@IDENTITY
IF
@COMMENT IS NOT NULL AND DATALENGTH(@Comment)>0
BEGIN
INSERT INTO PollComments (PollResultID, PollComment, IsAnon)
VALUES (@PollResultID, @Comment, @IsAnon)
END
END
select
SCOPE_IDENTITY()
-
update PollResults
set Rating = @Rating,
Weight = @Weight
WHERE
UserID = @user-id
AND CustomerID = @CustomerID
AND PollOptionID= @PollOptionID;
update PollComments
set PollComment = @PollComment,
IsAnon = @IsAnon
where PollResultID = @PollResultID;
-
Thank you!
-
I changed the stored procedure to the following, but only the piece that executes the PollResults update is working. The PollComments update does not function. Am I using the IDENTITY correctly?
Thanks!
ALTER
PROCEDURE [dbo].[UpdatePollResult]
@PollOptionID
int,
int,
@CustomerID
varchar(15),
@Rating
int,
@Weight
int,
@Comment
as ntext,
@IsAnon
as bit
AS
DECLARE
@PollResultID int
UPDATE
PollResults
SET
Rating = @Rating, Weight = @Weight
WHERE
(UserID = @user-id) AND (CustomerID = @CustomerID) AND (PollOptionID = @PollOptionID)
SELECT
@PollResultID = @@IDENTITY
UPDATE
PollComments
SET
PollComment = @Comment, IsAnon = @IsAnon, Approved = 0
WHERE
PollResultID = @PollResultID
-
You need to tell which row(s) to update. That's why Mark was using the PollID in the update statement. That ID must be sent as a parameter and not changed in the proc.
-
Thanks for the reply. But being that the first segment of the proc updates a row containing the PollResultID, isnt there a way to capture that value and pass it into the next statement that updates the pollComments table?
-
Please post the tables' definition so that we can see what's really going on.
-
Heres the definitions for the 2 tables im working with. 1 result to 1 comment. PollResultID is setup as an identity field. Thank you greatly for your assistance!
PollResults
PollResultID(PK, int, not null)
PollOptionID(FK, int, null)
UserID(int, not null)
CustomerID(varchar(15), null)
Rating(int, null)
Weight(int, null)
PollComments
PollCommentID(PK, int, null)
PollResultID(FK, int, null)
PollComment (text, null)
IsAnon (bit, null)
Approved(smallint, null)
-
ALTER PROCEDURE [dbo].[UpdatePollResult]
@PollResultID int,
@PollOptionID int,
int,
@CustomerID
varchar(15),
@Rating
int,
@Weight
int,
@Comment
as ntext,
@IsAnon
as bit
AS
DECLARE@PollResultIDintUPDATE PollResults
SET
Rating = @Rating, Weight = @Weight
WHERE
PollResultID = @PollResultID
SELECT@PollResultID = @@IDENTITYUPDATE
PollComments
SET
PollComment = @Comment, IsAnon = @IsAnon, Approved = 0
WHERE
PollResultID = @PollResultID
-
So this means that I have to pass the parameter (PollResultID)? I was trying to avoid that as Marks version of the Update PollResults section worked correctly. Thanks again, Larry.
-
Mark was assuming that you were sending in the parameter!!
. -
The following ended up working without having to pass the PollResultID parameter:
Thanks for both your responses and help!
USE
[DNN]
GO
/****** Object: StoredProcedure [dbo].[UpdatePollResult] Script Date: 08/10/2007 13:58:02 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
/** Create Stored Procedure **/
ALTER
PROCEDURE [dbo].[UpdatePollResult]
@PollOptionID
int,
int,
@CustomerID
varchar(15),
@Rating
int,
@Weight
int,
@Comment
as ntext,
@IsAnon
as bit
AS
DECLARE
@PollResultID int
UPDATE
PollResults
SET
Rating = @Rating, Weight = @Weight, @PollResultID = PollResultID
WHERE
(UserID = @user-id) AND (CustomerID = @CustomerID) AND (PollOptionID = @PollOptionID)
UPDATE
PollComments
SET
PollComment = @Comment, IsAnon = @IsAnon, Approved = 0
WHERE
PollResultID = @PollResultID
-
I can also enter my car without using the door but why the (?& would I want to do that???
You might consider taking a course on SQL, it would really come in handy.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply