一个查询的存储过程执行后返回的应该是一个结果集，有什么办法直接条件查询这个结果集呢

[scv1022]

一个查询的存储过程执行后返回的应该是一个结果集，有什么办法直接条件查询这个结果集呢，不用临时表和临时表变量，就是直接查询返回的结果集。
就是这样:
假设执行一个存储过程，比如 EXEC SP_SelectData
执行完之后返回查询得到的信息，比如：
A B C
-----------------
1 2 3
4 5 6
7 8 9
以上是执行完存储过程后返回的查询结果，
我的意思是，怎么条件查询以上的那个结果中的数据
比如：
查询A = 1 的数据。
不用临时表和临时表变量，因为数据字段较多时，建临时表和临时表变量都比较麻烦。
有什么方法直接条件查询返回的结果

[liangCK]

试试用TVF

[liangCK]

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
--WHERE 可以在这里再加上条件..
ORDER BY Sort;
GO

[昵称被占用了]

不用临时表和临时表变量只能用远程，但是应该是不推荐的

[scv1022]

用临时表我不太会写这是我的语句
CREATE PROCEDURE xxzx
@param varchar(50)
as
declare @sql varchar(8000)
set @sql = 'select recordid'
select @sql = @sql + ' , max(case propertyname when ''' + propertyname + ''' then stringvalue else null end) [' + propertyname + ']'
from (select distinct propertyname from GCMS_PROVALUE where unitcode=@param) as a
set @sql = @sql + ' from GCMS_PROVALUE where unitcode='''+@param+''' group by recordid'
exec(@sql)

GO

[水族杰纶]

--在存儲過程內將結果集賦予全局臨時變量##
EXEC SP_SelectData 
select * from ## where A=1

[scv1022]

liangCK 这个用的是函数，也不是很熟啊

[scv1022]

--在存儲過程內將結果集賦予全局臨時變量##
EXEC SP_SelectData
select * from ## where A=1
这个试试，说实话全局变量我都不会设啊，sql的编程基本不会

[水族杰纶]

if object_id('tempdb..#')is not null drop table #
go
create table # (A  int,B varchar(10), C varchar(10) )
 insert # exec P_test
select * from # where A=1

[scv1022]

CREATE PROCEDURE xxzx
@param varchar(50)
as
declare @sql varchar(8000)
set @sql = 'select recordid'
select @sql = @sql + ' , max(case propertyname when ''' + propertyname + ''' then stringvalue else null end) [' + propertyname + ']'
from (select distinct propertyname from GCMS_PROVALUE where unitcode=@param) as a
set @sql = @sql + ' from GCMS_PROVALUE where unitcode='''+@param+''' group by recordid'
exec(@sql)

就医我上面这个程序来说应该怎么写，wufeng4552请指教一下，就是加几句话的事，我急用。

[scv1022]

我的表的字段是活的不可以写死了

[等不到来世]

[Quote=引用 10 楼 scv1022 的回复:]
我的表的字段是活的不可以写死了
[/Quote]

select * 
FROM OPENROWSET('MSDASQL',
    'DRIVER={SQL Server};SERVER=local;UID=sa;PWD=',
    'EXEC SP_SelectData') AS t
where t.A=1

[seairhh]

关注中

[zzuyongp]

看不懂的就是好贴