如果是的话，这是否可能从包含多个选择语句的存储过程中检索单个结果集 [英] Is this possible retrieving single resultset from stored procdure that contains multiple select statements if yes than how

问题描述

如果不是，那么是否可能从包含多个选择语句的存储过程中检索单个结果集

Is this possible retrieving single resultset from stored procdure that contains multiple select statements if yes than how

推荐答案

如果要以以下方式获取数据:

If you want to fetch data in like these:

Select count(*)as greater12 from tbl_emp where age>12
Select count(*)as less12 from tbl_emp where age<12

在一个结果集中，使用 UNION命令 [

in the one result set, use UNION command[^].

SELECT 'Greater then 12' AS [Condition], COUNT([empId]) AS [HowMany]
FROM tbl_emp
WHERE [age] > 12
UNION ALL
SELECT 'Less then 12' AS [Condition], COUNT(empId) AS [HowMany]
FROM tbl_emp
WHERE [age] < 12

结果:

Result:

Condition         HowMany
Greater then 12   123
Less then 12      85

第二种方法是使用案例 [

The second way is to use CASE[^]

 SELECT [Info], COUNT([Info]) AS [HowMany] 
FROM (SELECT [Info] =
    CASE
         WHEN [age] <= 12 THEN 'Less or equal 12'
         WHEN [age] >12 AND [age] < 30 THEN 'Greater then 12 and less then 30'
         WHEN [age] >= 30 AND [age] < 50 THEN 'Equal or greater then 30 and less then 50'
         WHEN [age] >= 50 THEN 'Equal or greater then 50'
         ELSE 'Error!'
      END
FROM tbl_emp) AS DT
GROUP BY DT.[Info]
ORDER BY DT.[HowMany]

这可能不是正确的方法，但是您将获得提到的结果集

This might not be the right approach, but you will get result set as you mentioned

create proc usp_test
as
begin

declare @greater int, @lesser int
 SELECT @greater=COUNT([empId])FROM tbl_emp WHERE [age] > 12
 SELECT @lesser=COUNT([empId])FROM tbl_emp WHERE [age] < 12

SELECT @greater as Greaterthan12, @lesser as lessthan12

end

您可以使用union

You can use union

create proc usp_example
as
begin
    select id,name from tbluser
    union
    select id,name from tblproduct 
end

在两个选择中，stmt的列数应相同

In both select stmt should have same number of columns

这篇关于如果是的话，这是否可能从包含多个选择语句的存储过程中检索单个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！