存储 INSERT 查询 FROM 子句结果列中的 SQL 变量

发布于 2024-11-27 13:35:11 字数 419 浏览 1 评论 0原文

这对于大多数人来说可能是微不足道的，但我编写存储过程的时间并不长（仅 6 个月）。我希望能够根据用于 INSERT 查询的列之一设置变量 @testid 。我该怎么做？

DECLARE @testid INT;

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid

原文

This is probably trivial to most of you, but I haven't been writing stored procedures for very long (6 months only). I'd like to be able to set the variable @testid based on one of the columns being used for an INSERT query. How can I do this?

DECLARE @testid INT;

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid

分享到QQ

分享到微博

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

发布评论

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

土豪我们做朋友吧 2024-12-04 13:35:11

DECLARE @testid INT;

DECLARE @test TABLE (testid int);

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
OUTPUT INSERTED.testID INTO @test
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = testid FROM @test;

INSERT..SELECT.. 本质上是多行，因此它不允许语义允许将值分配给标量变量：应该将哪一行用于该值？

DECLARE @testid INT;

DECLARE @test TABLE (testid int);

INSERT INTO [exporttestresultreport] (
    [testid],
    [othercolumn]
) 
OUTPUT INSERTED.testID INTO @test
SELECT
    [testid],  -- <======= how can I set my variable based on this column?
    [othercolumn]
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = testid FROM @test;

An INSERT..SELECT.. is inherently multirow so it doesn't make semse to allow assigning a value to a scalar variable: what row should be used for the value?

回复收藏 0 原文

转身泪倾城 2024-12-04 13:35:11

DECLARE @testid INT;

DECLARE @t TABLE(t INT);

INSERT exporttestresultreport
(
    testid, othercolumn
)
OUTPUT INSERTED.testid INTO @t
SELECT testid, othercolumn 
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = t FROM @t;

-- not sure what you want to do if there are multiple rows in the insert

DECLARE @testid INT;

DECLARE @t TABLE(t INT);

INSERT exporttestresultreport
(
    testid, othercolumn
)
OUTPUT INSERTED.testid INTO @t
SELECT testid, othercolumn 
FROM 
    [exporttestresultreport] e
WHERE 
    [exporttestresultreportid] = @exporttestresultreportid;

SELECT @testid = t FROM @t;

-- not sure what you want to do if there are multiple rows in the insert

回复收藏 0 原文

~没有更多了~