Stored Procedure with select and update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure with select and update

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-07-15 : 07:32:40

Hey I want a stored procedure as follows :

1) First performs a select with several parameters, and for every record in this perform and update on that record

CREATE PROCEDURE [dbo].[spGetRowIdUserHistoryToSetExpiryDate]
(
//These are my four parameters
@userId INT = default,
@programmeId INT = default,
@stepId INT = default,
@dayIncrease INT
)
AS
BEGIN

SET NOCOUNT ON;

//This is my select
SELECT id,dateAdded
FROM tblUserQuestionnaireHistory
WHERE (userId = @userId) AND (programmeId = @programmeId) and stepId = @stepId and success = 1

END

//For every dateAdded and Id returned above I want to
create a new variable @UpdatedDate which is dateAdded + my variable @dayIncrease

update tblUserQuestionnaireHistory set dateExpired = @UpdatedDate where id = @id

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 08:06:20

You should be able to do the update in a single operation as follows:

UPDATE tblUserQuestionnaireHistory
SET    dateExpired = dateAdded + @dayIncrease
WHERE  userId = @userId
       AND programmeId = @programmeId
       AND stepId = @stepId
       AND success = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources