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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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