如何使用存储过程从SQL Server数据库中提取数据

Question

我的任务是为发布者应用程序创建一个存储过程，以从我们的SQL Server中检索员工数据。

目标：我们有EmpTable(让我们称之为源(。我们还有EmpData表。它有一个名为"Status"的列，默认值为"UNPROCESSED"。我们的目标是创建一个SP(让我们称之为SP1(，以便：它以100行为一批的方式轮询EmpTable中的所有数据。然后，它应该继续轮询此表中的数据，直到SP返回零记录为止。完成上述处理后，将创建/调用另一个SP(让我们称之为SP2(，以将状态更改为"已完成"。如果记录处理在应用程序中永久失败，即由于架构或验证错误等错误(无可重试(，SP2将状态更改为failed。然后将结果填充到EmpData表中。我们的目标是每天运行一次此批处理作业。希望这是有意义的

我想知道如何对此进行查询。我开始查询：

DECLARE @id_check INT
DECLARE @batchSize INT
DECLARE @results INT
SET @results = 1 --stores the row count after each successful batch
SET @batchSize = 100 --How many rows you want to operate on each batch
SET @id_check = 0 --current batch 
-- when 0 rows returned, exit the loop
WHILE (@results > 0) 
BEGIN
SELECT * -- This is just an example to generalize result for now
FROM empdata
SET @results = @@ROWCOUNT
-- next batch
SET @id_check = @id_check + @batchSize
END

我的目标是返回批次1以返回100个值，然后返回批次2以返回下一个100，依此类推

任何帮助都将不胜感激！

Answer 1

不幸的是，如果没有明确的要求，很难为您提供帮助。

但是，如果您想要的是批量提取所有匹配的记录(这没有多大意义(，那么您可以使用以下存储过程。

它将返回符合任何条件的前100行。然后，当它们加载到您的应用程序中时，您会再次调用SP，传入您在上一个记录集中收到的最大ID。

您的应用程序将继续调用此SP，直到没有返回任何行为止。

CREATE OR ALTER PROCEDURE dbo.MyTestProcedure1
(
-- Pass in the last ID processed
@LastIdChecked int = 0
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SET @LastIdChecked = COALESCE(@LastIdChecked,0);
DECLARE @BatchSize int = 100;
-- Return the next @BatchSize records after the last Id checked
SELECT TOP(@BatchSize) *
FROM dbo.EmpTable
WHERE Id > @LastIdChecked
ORDER BY Id ASC;
RETURN 0;
END;

一个更令人期待的过程是，您使用SP提取前100条记录，然后在应用程序中完全处理它们。然后，您再次调用SP，SP就会知道哪些记录已被处理，并在WHERE子句中将它们过滤掉。然后运行此操作直到完成。

这个解决方案看起来是这样的：

CREATE OR ALTER PROCEDURE dbo.MyTestProcedure2
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
DECLARE @BatchSize int = 100;
-- Return the next @BatchSize records
SELECT TOP(@BatchSize) *
FROM dbo.EmpTable T
WHERE {Record is unprocessed}
-- e.g. if you are expeceting 1 record a day per dbo.EmpTable record.
-- WHERE NOT EXISTS (
--    SELECT 1
--    FROM dbo.EmpData D
--    WHERE T.EmpId = D.EmpId AND D.Date = CONVERT(date, GETDATE())
--)
ORDER BY Id ASC;
RETURN 0;
END;