如何在不使用 temp 的情况下从存储过程的结果集中选择一列。桌子？

发布于 2024-10-15 02:39:56 字数 792 浏览 3 评论 0原文

我在 SQL Server 2005 中有一个存储过程。它在一行中返回三个变量，如下（极其简化）示例：

CREATE PROCEDURE proc1
AS
   DECLARE @col1 VARCHAR(4)
   DECLARE @col2 VARCHAR(4)
   DECLARE @col3 VARCHAR(4)
   SET @col1 = 'val1'
   SET @col2 = 'val2'
   SET @col3 = 'val3'

   SELECT @col1 col1, @col2 col2, @col3 col3
GO

在外部应用程序中，我需要执行存储过程并处理三个值之一。由于此应用程序的限制，它将始终仅使用第一列。

因此，我正在寻找一种将过程的结果集限制为一列或重新排列列的顺序的方法。

IIRC MySQL支持如下子查询，但SQL Server不支持：

SELECT col1 FROM (EXEC proc1)

我知道我可以使用临时表（或表变量）来缓冲结果，然后选择一列，但我希望有一种更“简洁”的方式比这个：

CREATE TABLE #tmp (col1 VARCHAR(4) NOT NULL, col2 VARCHAR(4) NOT NULL, col3 VARCHAR(4) NOT NULL)
INSERT INTO #tmp EXEC proc1
SELECT col2 FROM #tmp
DROP TABLE #tmp

预先感谢您提供的任何提示，

帕特里克

原文

I have a stored procedure in SQL Server 2005. It returns three variables in one single line, like this (extremely simplified) example:

CREATE PROCEDURE proc1
AS
   DECLARE @col1 VARCHAR(4)
   DECLARE @col2 VARCHAR(4)
   DECLARE @col3 VARCHAR(4)
   SET @col1 = 'val1'
   SET @col2 = 'val2'
   SET @col3 = 'val3'

   SELECT @col1 col1, @col2 col2, @col3 col3
GO

In an external application, I need to execute the stored procedure and process one of the three values. Due to limitation in this application, It will always only use the very first column.

So, what I'm looking for is a way to limit the result set of the procedure to one column or rearrange the order of the columns.

IIRC MySQL supports subqueries like the following, but SQL Server does not:

SELECT col1 FROM (EXEC proc1)

I know that I can use a temporary table (or table variable) to buffer the result and then select one column, but I hope that there is a more "condensed" way than this:

CREATE TABLE #tmp (col1 VARCHAR(4) NOT NULL, col2 VARCHAR(4) NOT NULL, col3 VARCHAR(4) NOT NULL)
INSERT INTO #tmp EXEC proc1
SELECT col2 FROM #tmp
DROP TABLE #tmp

Thanks in advance for any hints you can give,

Patrick

分享到QQ

分享到微博