Writing a SP, with a select and then 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

Writing a SP, with a select and then update

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-12 : 06:30:02

Hey Basically I am looking for a Stored Procedure with 2 Parameters

programmeId INT
increaseDays INT

Which Runs the following select query
SELECT id,dateExpired
FROM tblUserQuestionnaireHistory
WHERE (dateExpired IS NOT NULL) AND (programmeId = 23)

Which Returns values such as
212 21/05/2011 00:00:00

And then for all results above I want to add the number of days coming from "increaseDays". So basically I am trying to increase the dateExpired by a certain number of days.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-12 : 06:35:28

[code]
SELECT id, dateExpired = dateadd(day, increaseDays ,dateExpired)
FROM tblUserQuestionnaireHistory
WHERE (dateExpired IS NOT NULL) AND (programmeId = 23)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-12 : 06:38:50

Cool and then How Can I update the rows in the same stored procedure ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-12 : 06:45:52

you have to use a separate UPDATE statement


UPDATE t
SET    <some col> = <some value>
FROM   tblUserQuestionnaireHistory t
WHERE  <some condition>

KH
[spoiler]Time is always against us[/spoiler]

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-12 : 06:51:05

Yup, I was wondering how I could do both in same stored procedure

i.e run the select, and based on the results of the select, perform the update query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-12 : 06:57:45

oh i see what you want.


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

KH
[spoiler]Time is always against us[/spoiler]

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-12 : 09:52:21

Works great thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources