如何有选择地返回 SQL Server 上的存储过程中的行? [英] How to selectively return rows inside a stored procedure on SQL Server?

问题描述

我有一个基本存储过程，只是从数据库中返回一个选择，如下所示:

I have a base stored procedure simply returning a select from the database, like this:

CREATE PROCEDURE MyProcedure
AS
BEGIN
  SELECT * FROM MyTable
END
GO

但现在我需要为选择的每一行执行一些逻辑.根据结果​​我是否需要返回这一行.我会让我的 select 语句使用游标运行，检查规则并返回或不返回该行.像这样:

But now I need to execute some logic for every row of my select. According to the result I need to return or not this row. I would have my select statement running with a cursor, checking the rule and return or not the row. Something like this:

CREATE PROCEDURE MyProcedure
AS
BEGIN

  DECLARE CURSOR_MYCURSOR FOR SELECT Id, Name FROM MyTable
  OPEN CURSOR_MYCURSOR
  FETCH NEXT FROM CURSOR_MYCURSOR INTO @OUTPUT1, @OUTPUT2

  WHILE (@@FETCH_STATUS=0)
  BEGIN

  IF (SOME_CHECK)
    SELECT @OUTPUT1, @OUTPUT2
  ELSE
    --WILL RETURN SOMETHING ELSE

  END

END
GO

第一个问题是，每次我执行 SELECT @OUTPUT1, @OUTPUT2 时，行都会作为不同的结果集发回，而不是我需要的单个表中.

The first problem is that everytime I do SELECT @OUTPUT1, @OUTPUT2 the rows are sent back as different result sets and not in a single table as I would need.

当然，对一行应用一些逻辑听起来像是一项FUNCTION"工作.但是我不能使用函数的结果来过滤被选择的结果.那是因为当我的支票返回 false 时，我需要选择其他东西来替换错误的行.所以，我需要返回错误的行，以便我可以知道它们并替换为其他行.

Sure, applying some logic to a row sounds like a "FUNCTION" job. But I can't use the result of the function to filter the results being selected. That is because when my check returns false I need to select something else to replace the faulty row. So, I need to return the faulty rows so I can be aware of them and replace by some other row.

这个方法的另一个问题是我需要声明很多变量，以便我可以通过游标迭代输出它们.并且这些变量需要遵循原始表属性的数据类型，并且如果原始表发生某些变化，则不会以某种方式失去同步.

The other problem with this method is that I would need to declare quite a few variables so that I can output them through the cursor iteration. And those variables would need to follow the data types for the original table attributes and somehow not getting out of sync if something changes on the original tables.

那么，根据条件返回单个结果集的最佳方法是什么?

So, what is the best approach to return a single result set based on a criteria?

提前致谢.

推荐答案

我建议使用游标，但解决您的问题的简单方法是使用 table variable 或 temp table

I recommend use of cursors but easy solution to your question would be to use table variable or temp table

DECLARE @MyTable TABLE
    (
     ColumnOne VARCHAR(20)
    ,ColumnTwo VARCHAR(20)
    )

CREATE TABLE #MyTable
    (
     ColumnOne VARCHAR(20)
    ,ColumnTwo VARCHAR(20)
    )

<小时>

比在你的游标内你可以插入符合你逻辑的记录

---

than inside your cursors you can insert records that match your logic

INSERT INTO @MyTable VALUES (@Output1, @Output2)
INSERT INTO #MyTable VALUES (@Output1, @Output2)

<小时>

完成光标后只需select表格中的所有内容

SELECT * FROM @MyTable
SELECT * FROM #MyTable

这篇关于如何有选择地返回 SQL Server 上的存储过程中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！