Return multiple values using stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 8-Feb-16 11:23am

Member 12111217

Updated 26-Feb-16 23:01pm

Add a Solution

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

Add a Solution

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

RDBurmon · Answer 1 · 2016-02-26T23:00:00

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

Return multiple values using stored procedure

1 solution

Solution 2

Add your solution here

Preview 0

Return multiple values using stored procedure

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us