Click here to Skip to main content
15,068,988 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table which has columns as below:
SELECT TOP 1000 [User_ID] 
  FROM [DB].[dbo].[User]

I want to make a stored procedure to return all User_ID where Created_Date is past more than 7 days from today. So for today, the stored procedure should return all user_id before 2/2/2016.

And I want to update the expired as true in all returned user_id rows.

One SP should include all these processes.

Please help me solving this

Thank you

What I have tried:

I have no idea how to return all values from select statement.
Updated 26-Feb-16 23:01pm
PIEBALDconsult 8-Feb-16 19:35pm
Why not just UPDATE expired=true WHERE Created_Date < seven days ago?
Member 12111217 8-Feb-16 21:02pm
Yes, that was the easy way to do it. Thanks!
PIEBALDconsult 8-Feb-16 21:09pm
Easier tends to be better. Always seek a way you can just use a simple SQL statement rather than use a cursor and/or fetch the data to the client and back.

1 solution

Its very simple actually - Here is the procedure

Table considered for this example
CREATE TABLE [User] ([User_ID] INT,[Group_ID] INT,[User_Session_ID] INT,[Created_Date] datetime,[User_Expired] bit)

Insert some data so that we can test the proc
insert into [User] 
select 1,1,1,Dateadd(dd,-6,GetDate()),0
select 2,2,2,Dateadd(dd,-8,GetDate()),0

Here is the procedure which updates the user profiles by marking them as expired and returns the updated user ids

CREATE PROCEDURE GetAllUserswhosProfilesExpired

UPDATE [User] SET [User_Expired]=1 WHERE Created_Date>=Dateadd(dd,-7,GetDate())
SELECT * FROM [User] WHERE Created_Date>=Dateadd(dd,-7,GetDate())

Try executing the proc
EXEC GetAllUserswhosProfilesExpired

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900