我有一个表#data
其中包含Id
列和Count
列。此外,我还有一个存储过程MyProc
它接受参数@id
(等于Id
列(并返回数据集(计数等于Count
列(。
我的目标是从Id
分配Count
列,MyProc
没有游标。
我知道,这样的事情不起作用:
UPDATE d
SET Count = (SELECT COUNT(*) FROM (EXEC MyProc d.Id))
FROM #data AS d
是否有我不知道的语法,或者光标是实现此目的的唯一选择?
PS:这对我来说是一个代码质量和性能问题。调用存储过程是最简单的方法,无需重复 50 行 SQL,但游标会减慢它的速度。
我相信您可以使用以下查询:
IF OBJECT_ID('dbo.data') IS NOT NULL DROP TABLE data;
IF OBJECT_ID('dbo.MyFunct') IS NOT NULL DROP FUNCTION dbo.MyFunct;
GO
CREATE TABLE data
(
ID int,
[Count] int
);
INSERT data VALUES (1,5), (1,10), (2,3), (4,6);
GO
UPDATE d
SET d.[Count] = f.CNT
FROM
(SELECT ID,COUNT(id) AS CNT FROM data GROUP BY ID) f
INNER JOIN data d ON f.ID = d.ID
我找不到使用存储过程的方法。需要您可以使用表值函数:
CREATE FUNCTION dbo.MyFunct(@id INT)
RETURNS @i TABLE
(ID INT , CNT INT)
AS
BEGIN
INSERT INTO @i
SELECT ID,COUNT(id) AS CNT FROM data GROUP BY ID
RETURN
END;
GO
UPDATE d
SET d.[Count] = f.CNT
FROM dbo.MyFunct(1) f INNER JOIN data d ON f.ID = d.ID
要按照你所说的去做,你需要一个函数,而不是一个过程。
CREATE FUNCTION dbo.myFunc (@Id INT)
RETURNS INT
AS
BEGIN
UPDATE someTable
SET someCol = 'someValue'
WHERE id = @Id;
RETURN @@ROWCOUNT;
END
GO
然后在更新语句中调用该函数;
UPDATE d
SET d.Count = dbo.myFunc(d.Id)
FROM #data AS d;
但是,基于行的操作是一种不好的做法。您应该始终努力执行基于集合的操作,但是由于我不知道您的过程是做什么的,因此我只能对您应该做什么(根本不使用过程(进行疯狂的猜测:
DECLARE @data TABLE (Id INT);
UPDATE x
SET x.someCol = 'SomeVal'
OUTPUT INSERTED.id INTO @data
FROM someTable AS x
INNER JOIN #data AS d
ON d.Id = x.Id;
WITH cte (Id, myCount) AS (
SELECT d.Id
,COUNT(d.Id) AS myCount
FROM @data AS d
GROUP BY d.Id
)
UPDATE d
SET d.[Count] = c.myCount
FROM #data AS d
INNER JOIN cte AS c
ON c.Id = d.Id;
<</div>
div class="one_answers">我不完全明白你想做什么,但我认为你的解决方案将涉及@@ROWCOUNT;观察:
-- Sample data and proc...
----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data;
IF OBJECT_ID('dbo.MyProc') IS NOT NULL DROP PROC dbo.MyProc;
GO
CREATE TABLE #data
(
id int,
[Count] int
);
INSERT #data VALUES (1,5), (1,10), (2,3), (4,6);
GO
CREATE PROC dbo.MyProc(@id int)
AS
BEGIN
SELECT 'some value'
FROM #data
WHERE @id = id;
END;
GO
之前的数据:
id Count
----------- -----------
1 5
1 10
2 3
4 6
使用@@ROWCOUNT的例程
DECLARE @someid int = 1; -- the value you're passing to your proc
EXEC dbo.MyProc 1;
DECLARE @rows int = @@ROWCOUNT; -- this is what you need.
UPDATE #data
SET [Count] = @rows
WHERE id = @someid;
之后的数据
id Count
----------- -----------
1 2
1 2
2 3
4 6