使用存储过程返回多个值 [英] Return multiple values using stored procedure

问题描述

我有一个表格，其中包含以下列：

I have a table which has columns as below:

SELECT TOP 1000 [User_ID] 
      ,[Group_ID]
      ,[User_Session_ID]
      ,[Created_Date]
      ,[User_Expired]
  FROM [DB].[dbo].[User]

我想创建一个存储过程来返回Created_Date从今天起超过7天的所有User_ID。因此，对于今天，存储过程应在2016年2月2日之前返回所有user_id。



我想在所有返回的user_id行中更新过期为真。



一个SP应该包括所有这些过程。



请帮我解决这个问题



谢谢



我尝试了什么：



我不知道如何从select语句中返回所有值。

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.

推荐答案

实际上非常简单 - 这是程序



本例考虑的表格

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)

插入一些数据o我们可以测试proc

Insert some data so that we can test the proc

insert into [User] 
select 1,1,1,Dateadd(dd,-6,GetDate()),0
UNION
select 2,2,2,Dateadd(dd,-8,GetDate()),0

以下是通过将用户配置文件标记为已过期来更新用户配置文件并返回更新的用户ID的过程

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

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

尝试执行proc

Try executing the proc

EXEC GetAllUserswhosProfilesExpired

这篇关于使用存储过程返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！