Need a select query after 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

Need a select query after update

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-20 : 10:24:21

Hey I currently have the following query in a stored procedure

ALTER PROCEDURE [dbo].[spUpdateExpiryDates]
(
@programmeId INT = default,
@increaseDays INT = default
)
AS
BEGIN

SET NOCOUNT ON;

UPDATE t
SET dateExpired = a.dateExpired
FROM tblUserQuestionnaireHistory t
INNER JOIN
(
SELECT id, dateExpired = dateadd(day, @increaseDays ,dateExpired)
FROM tblUserQuestionnaireHistory
WHERE (dateExpired IS NOT NULL) AND (programmeId = @programmeId)
) a ON t.id = a.id
END

And it works fine, it basically updates a DATETIME column for a certain group of people part of programme X.

What I want to add to the query is a Select Query to wrap around it or something, which after the query returns to me all people who after the DATETIME has been updated, this DATETIME is now in the past.

So if the update to the dateExpired columns caused this datetime to go in the past, I want all those people to be returned in the select.

Any Idea ?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-20 : 13:43:43

May be you can get back the IDs via executing the inline query of your update statement

SELECT id
FROM tblUserQuestionnaireHistory
WHERE (dateExpired IS NOT NULL) AND (programmeId = @programmeId)
And dateExpired = dateadd(day, -(@increaseDays) ,dateExpired)

Join this set with required tables in order to get the desired people information

Cheers
MIK

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-20 : 14:16:09

or use the OUTPUT clause and updated.id

or some such.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources