CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user971741
user971741

Reputation:

Getting result of multiple SELECT statements from stored procedure

I am practicing on creating a stored procedure which has multiple select queries in it and I want to return back the result of all these queries back as one dataset so I could put them in a array.

But from what I have written my stored procedure only return the results of first query and not the rest.

CREATE PROCEDURE getPost
AS
SET NOCOUNT ON
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by dtetme DESC;
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by dtetme DESC;
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by [hits] DESC;
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name='Small') order by dtetme DESC;
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name=’Medium’) order by dtetme DESC;
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name='Big’) order by dtetme DESC;
SELECT TOP 3 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by newid();
GO

I am using MS SQL Server. Kindly Help!

Upvotes: 0

Views: 12061

Answers (2)

user806549
user806549

Reputation:

You could place a UNION between each SELECT

CREATE PROCEDURE getPost
AS
SET NOCOUNT ON
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by dtetme     DESC
UNION
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by dtetme DESC
UNION
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by [hits] DESC
UNION
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name='Small') order by dtetme DESC
UNION
SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name=’Medium’) order by dtetme DESC
UNION
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 AND category=(SELECT id FROM category where name='Big’) order by dtetme DESC
UNION
SELECT TOP 3 title,summary,cphoto,pId FROM [post] WHERE [status]=1 order by newid();
GO

Note that if you use UNION, you won't get any duplicates and your query will probably be slower on large datasets. If you use UNION ALL instead you may get duplicates, but the query will be faster.

Upvotes: 1

Andomar
Andomar

Reputation: 238276

Union them together

SELECT TOP 5 title,summary,cphoto,pId FROM [post] WHERE [status]=1 
UNION ALL
SELECT TOP 1 title,summary,cphoto,pId FROM [post] WHERE [status]=1 
UNION ALL
...
order by title DESC;

Upvotes: 0

Related Questions