如何将类似 C# 的代码转换为 SQL Server 存储过程

发布于 2024-11-10 13:12:11 字数 767 浏览 3 评论 0原文

下面是我需要编写的 SQL Server 存储过程的伪代码：

int[] followers = (select FollowerID from StoreFollower where StoreId = @storeId)

insert into Message (senderId, recipientId)
values (@senderId, followers[0])

int rootMessageId = last_id()

foreach (int follower in followers.Skip(1))
    insert into Message (senderId, recipientId, rootMessageId)
    values (@senderId, follower, rootMessageId

它获取所有 Store 的关注者 ID，在 Message 中为第一个关注者创建一条记录。然后，它为每个后续关注者 ID 创建一条 Message，同时指定批次中第一个 Message 记录的 ID。

我需要将其转换为 SQL Server 存储过程，但我以前从未编写过，所以我很犹豫。我应该使用 table 变量来保存 select 结果吗？我应该使用数组吗？这里与 foreach 最接近的匹配是什么？如何切掉第一个元素？

我非常感谢这样的过程的草图，只是为了知道要进一步看什么。

原文

Below is pseudo-code for SQL Server stored procedure I need to write:

int[] followers = (select FollowerID from StoreFollower where StoreId = @storeId)

insert into Message (senderId, recipientId)
values (@senderId, followers[0])

int rootMessageId = last_id()

foreach (int follower in followers.Skip(1))
    insert into Message (senderId, recipientId, rootMessageId)
    values (@senderId, follower, rootMessageId

It gets all Store's follower IDs, creates a record in Message for the first one. Then it creates a Message for each subsequent follower ID, also specifying ID of the first Message record in the batch.

I need to convert this to SQL Server stored procedure, however I never wrote one before so I'm hesitant. Should I use a table variable to hold select result? Should I use arrays? What is the closest match to foreach here? How do I slice off the first element?

I would very much appreciate a sketch of such proc, just to know what to look further at.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灯下孤影 2024-11-17 13:12:11

我在 T-SQL 中的尝试。我假设 (a) FollowerID 是 int，(b) @storeId 和 @senderID ar 参数存储过程。

DECLARE @FirstFollower int
DECLARE @FirstMessage int

--Get the first follower (i.e. smallest FollowerID, hopefully that's what you mean
--otherwise you need an appropriate ORDER BY clause here
SELECT @FirstFollower=TOP(1) FollowerId from StoreFollower 
where StoreId = @storeId

--Store message for first follower and get root message id
INSERT INTO Message (senderId, recipientId)
VALUES(@senderId, @FirstFollower)

SELECT @FirstMessage=SCOPE_IDENTITY()

--store a message per follower except the first. Same conditions apply here
--regarding the order of the followers as in the first SELECT query
INSERT INTO Message(senderId, recipientId, rootMessageId)
SELECT @senderId, FollowerID, @FirstMessage
FROM StoreFollower WHERE
FollowerID <> @FirstFollower

华泰

My stab at it in T-SQL. I assume that (a) FollowerID is int, (b) @storeId and @senderID ar parameters of the stored procedure.

DECLARE @FirstFollower int
DECLARE @FirstMessage int

--Get the first follower (i.e. smallest FollowerID, hopefully that's what you mean
--otherwise you need an appropriate ORDER BY clause here
SELECT @FirstFollower=TOP(1) FollowerId from StoreFollower 
where StoreId = @storeId

--Store message for first follower and get root message id
INSERT INTO Message (senderId, recipientId)
VALUES(@senderId, @FirstFollower)

SELECT @FirstMessage=SCOPE_IDENTITY()

--store a message per follower except the first. Same conditions apply here
--regarding the order of the followers as in the first SELECT query
INSERT INTO Message(senderId, recipientId, rootMessageId)
SELECT @senderId, FollowerID, @FirstMessage
FROM StoreFollower WHERE
FollowerID <> @FirstFollower

HTH

回复收藏 0 原文

~没有更多了~