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:
SQL
SELECT TOP 1000 [User_ID] 
      ,[Group_ID]
      ,[User_Session_ID]
      ,[Created_Date]
      ,[User_Expired]
  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.
Posted
Updated 26-Feb-16 23:01pm
Comments
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
SQL
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
SQL
insert into [User] 
select 1,1,1,Dateadd(dd,-6,GetDate()),0
UNION
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

SQL
CREATE PROCEDURE GetAllUserswhosProfilesExpired
AS
BEGIN

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



Try executing the proc
SQL
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