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

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Posted 29-May-12 1:22am

Pratika05

Add a Solution

Comments

Prosan 29-May-12 7:50am

in your all select statememnt is no of columns is same than you can use union

Prosan 29-May-12 8:20am

is this query solve your problem. i hope it will return result as you want.

4 solutions

Solution 3

If you want to fetch data in like these:

SQL

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

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

SQL

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[^]

SQL

 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]

Posted 29-May-12 1:56am

Maciej Los

Updated 29-May-12 2:08am

v2

Comments

Sandeep Mewara 29-May-12 14:13pm

5!

Maciej Los 29-May-12 14:33pm

Thank you, Sandeep ;)

Solution 2

in your all select statememnt if no. of columns is same than you can use union it will return single resultset.
now you can write this query as i telling you

SQL

select (Select count(*)as greater12 from tbl_emp where age>12 ) + ' ' + (Select count(*)as less12 from tbl_emp where age<12 ) as Res

Posted 29-May-12 1:52am

Prosan

Updated 29-May-12 2:02am

v3

Comments

Pratika05 29-May-12 7:55am

But I need result in tabular Format Like I have query Select count(*)as greater12 from tbl_emp where age>12 Select count(*)as less12 from tbl_emp where age<12 Now I want this Type of result greater12 less12 34 2

Prosan 29-May-12 8:19am

is this query solve your problem?

Solution 1

You can use union

SQL

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

In both select stmt should have same number of columns

Posted 29-May-12 1:45am

Ganga Patangi

Updated 29-May-12 1:59am

v2

Comments

Pratika05 29-May-12 7:48am

But I need result in tabular Format Like I have query
Select count(*)as greater12 from tbl_emp where age>12
Select count(*)as less12 from tbl_emp where age<12
Now I want this Type of result
greater12 less12
34 2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ganga Patangi · Accepted Answer · 2012-05-29T02:05:00

Solution 4

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

SQL

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

Posted 29-May-12 2:05am

Ganga Patangi

Updated 29-May-12 2:09am

v2

Comments

Pratika05 29-May-12 8:14am

thanx i got but does this way ever give me error oor wrong output

Ganga Patangi 29-May-12 8:21am

Nope...you can use this...You wont get any errors or wrong output...