根据参数执行正确的存储过程

I found 2 Q/A that are the same (according to me) but for which the given answers are different and so I wanted your opinion upon the right answer (or maybe this is actually not the same question...)

我发现有两个Q/A是一样的(根据我的说法)，但是给出的答案是不同的，所以我想让你的观点得到正确的答案(或者这实际上不是同一个问题…)

Here are the questions:

这是问题:

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. Which approach should you use?

您的应用程序包含每个国家的存储过程。每个存储过程通过@EmpID参数接受员工标识号。您需要为每个雇员构建一个单独的进程，该进程将根据居住国执行适当的存储过程。您应该使用哪种方法?

1. A SELECT statement that includes CASE
2. 包含CASE的SELECT语句
3. Cursor
4. 光标
5. BULK INSERT
6. 批量插入
7. View
8. 视图
9. A user-defined function
10. 一个用户定义的函数

Correct Answer: 5 (A user-defined function)

正确答案:5(用户定义函数)

The second:

第二个:

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

您的应用程序包含每个国家的存储过程。每个存储过程通过@EmpID参数接受员工标识号。您计划为每个雇员构建一个单独的进程，该进程将根据居住国执行存储过程。您应该使用哪种方法?

1. A recursive stored procedure
2. 一个递归的存储过程
3. Trigger
4. 触发
5. An UPDATE statement that includes CASE
6. 包含CASE的更新语句
7. Cursor
8. 光标
9. The foreach SQLCLR statement
10. foreach SQLCLR声明

Correct Answer: 4 (Cursor)

正确答案:4(指针)

In my opinion, the Cursor solution was right...

在我看来，光标解决方案是正确的……

2 个解决方案

#1

The first is a bit strange. The documentation for creating a function explicitly says:

第一个有点奇怪。创建函数的文档明确地说:

• User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
• 用户定义的函数不能调用存储过程，但可以调用扩展的存储过程。

It is unclear what author's intention is, but my reading of the question rules of answer (5).

不清楚作者的意图是什么，但我对问题的回答规则(5)的阅读。

I might not prefer a cursor for the work, but of the options specified, it would seem to be the only applicable solution.

我可能不喜欢在工作中使用游标，但是在指定的选项中，它似乎是惟一适用的解决方案。

#2

On the first question, you cannot call a stored procedure from a select, insert, a view, or a function, so that only leaves a cursor. But that is a poor answer. A cursor is not "a single process". It is a pointer, a tool you can use within a single process. So if you use a cursor, to loop through a series of employee records, you are probably within a stored procedure.

在第一个问题上，您不能从select、insert、视图或函数调用存储过程，因此只留下游标。但这是一个糟糕的回答。游标不是“单个进程”。它是一个指针，一个你可以在一个进程中使用的工具。因此，如果您使用游标来循环一系列员工记录，您很可能是在一个存储过程中。

To me that would be the answer, "a stored procedure", which is included in your second question, kind of. In the second question it says "recursive stored procedure". The recursive part bothers me because the stored procedure does not need to be recursive to call another stored procedure. So option 1 on question 2 is halfway decent, but has problems.

对我来说，这就是答案，“存储过程”，包含在第二个问题中。在第二个问题中，它说“递归存储过程”。递归部分困扰我，因为存储过程不需要递归调用另一个存储过程。因此，问题2中的选项1还算不错，但有问题。

I rule out options 3,4 and 5 on the second question for the same reasons as I ruled out all options in question 1. That leaves the Trigger. It is entirely possible that you could call this country stored procedure on a trigger that runs anytime a new employee is inserted or updated. That might be a good solution depending on the application. So that leaves question 2 with one and a half good answers (#2 and #1).

我排除了第二个问题的选项3、4和5，理由与我排除了问题1中的所有选项相同。这使得扳机。在插入或更新新员工时，您完全有可能在触发器中调用这个国家存储过程。根据应用程序的不同，这可能是一个很好的解决方案。所以剩下的问题2有一个半好的答案(#2和#1)。

Bottom line, question 1 has no really good answers, and question 2 has two that are at least semi-acceptable. If it were me asking the question, I would use the options from question 1, but replace option number 2 (the cursor) with "A stored procedure".

底线，问题1没有很好的答案，问题2有两个至少是半可接受的。如果是我问这个问题，我会使用问题1中的选项，但是用“存储过程”替换选项2(光标)。