如何在select查询中执行存储过程

SELECT col1,
       col2,
       col3,

EXEC GetAIntFromStoredProc(T.col1) AS col4
     FROM Tbl AS T
     WHERE (col2 = @parm)

How to write this SQL query in SQL Server 2008?

如何在SQL Server 2008中编写这个SQL查询?

3 个解决方案

#1

Thanks @twoleggedhorse.

谢谢@twoleggedhorse。

Here is the solution.

这是解决方案。

First we created a function

首先，我们创建了一个函数。

CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER

AS
BEGIN
   DECLARE @id INTEGER

   set @id= (select TOP(1) id From tbl where col=@parm)

   RETURN @id
END

then we do the select query

然后我们执行select查询

Select col1, col2, col3,
GetAIntFromStoredProc(T.col1) As col4
From Tbl as T
Where col2=@parm

#2

Functions are easy to call inside a select loop, but they don't let you run inserts, updates, deletes, etc. They are only useful for query operations. You need a stored procedure to manipulate the data.

函数在select循环中很容易调用，但是它们不允许运行插入、更新、删除等等。它们只对查询操作有用。您需要一个存储过程来操作数据。

So, the real answer to this question is that you must iterate through the results of a select statement via a "cursor" and call the procedure from within that loop. Here's an example:

因此，这个问题的真正答案是，您必须通过“游标”遍历select语句的结果，并从该循环中调用过程。这里有一个例子:

DECLARE @myId int;
DECLARE @myName nvarchar(60);
DECLARE myCursor CURSOR FORWARD_ONLY FOR
    SELECT Id, Name FROM SomeTable;
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId, @myName;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE dbo.myCustomProcedure @myId, @myName;
    FETCH NEXT FROM myCursor INTO @myId, @myName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;

Note that @@FETCH_STATUS is a standard variable which gets updated for you. The rest of the object names here are custom.

注意@ fetch_status是一个标准变量，它会为您更新。这里剩下的对象名是自定义的。

#3

As long as you're not doing any INSERT or UPDATE statements in your stored procedure, you will probably want to make it a function.

只要您没有在存储过程中执行任何插入或更新语句，您可能希望将其作为函数。

Stored procedures are for executing by an outside program, or on a timed interval.

存储过程是用来由外部程序执行的，或者是在一个定时的间隔内执行的。

The answers here will explain it better than I can:

这里的答案会比我更好地解释它:

Function vs. Stored Procedure in SQL Server

SQL Server中的函数和存储过程

#1