MS-SQL 查询 - 通过存储过程检索更新记录 [英] MS-SQL Query - Update record retrieved via Stored Procedure

问题描述

*更新 *

范围现在已经略有发展，我现在需要检索字段Id、uri、linkTo".这如何改变事情???

The scope has now developed slightly, and I now need to retreive the fields 'Id, uri, linkTo'. How does this change things???

我使用的是 MS-SQL 2005 数据库并有以下存储过程；

I'm using an MS-SQL 2005 db and have the following stored procedure;

CREATE PROCEDURE dbo.getNewAds
(
@region
)
AS
BEGIN
SELECT TOP 1 Id, uri, linkTo FROM Adverts
ORDER BY NEWID()
WHERE adRegion = @region
END

然后，对于检索到的单个结果，我想在同一个表中名为 adShown 的列中添加1".在同一过程中执行此操作的最简单/最快的方法是什么?

I would like to then add '1' to the column named adShown in the same table, for the single result retrieved. What is the simplest / quickest means to do this within the same procedure?

预先感谢您提供的任何帮助.

Thanks in advance for any help you can offer.

推荐答案

有一种古怪的更新语法，可以让您在执行更新的同时将值分配给变量，但我不会展示因为它没有记录，不受支持，而且我不能保证它会在 SQL Server 的未来版本中继续工作.这是我推荐的方法(假设 Id 是 PK 或至少是唯一的):

There is a quirky update syntax that will let you assign the value to a variable at the same time as performing an update, but I'm not going to show it to you because it is undocumented, unsupported, and I can't guarantee that it will continue to work in future versions of SQL Server. Here is the method I would recommend (assuming Id is PK or at least unique):

DECLARE 
  @Id INT, 
  @uri VARCHAR(2048),
  @linkTo VARCHAR(2048);

SELECT TOP 1 @Id = Id, @uri = uri, @linkTo = linkTo
  FROM dbo.Adverts
  WHERE adRegion = @region
  ORDER BY NEWID();

UPDATE dbo.Adverts
  SET adShown = adShown + 1
  WHERE Id = @Id;

SELECT Id = @Id, uri = @uri, linkTo = @linkTo;

这篇关于MS-SQL 查询 - 通过存储过程检索更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！