CODEWITHSUNDEEP
c#asp.netmysqlvisual-studio-2010
user2384794
user2384794

Reputation: 63

how to get data from multiple tables in a single stored procedure?

i have three tables table1, table2, and table3. And following is my stored procedure. after executing the stored procedure i got the following out put.

alter PROCEDURE test

    @SDate datetime,
    @EDate datetime

As

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    Select count(quoteid) as TotalQuote, sum(totalamount) as QuoteAmount from dbo.QuoteBase
    where CreatedOn BETWEEN @SDate AND @EDate

    select  count(salesorderid)as TotalOrders, sum(totalamount) as OrderAmount from dbo.SalesOrderBase Where
    CreatedOn BETWEEN @SDate AND @EDate

    select count(opportunityid)as TotalSales  from dbo.OpportunityBase Where
    CreatedOn BETWEEN @SDate AND @EDate


GO

Output image

but i don't want to display the query result separately.

How can i done the job so that the query results will be in a single line?

Upvotes: 1

Views: 5882

Answers (1)

AAlferez
AAlferez

Reputation: 1492

SELECT * FROM (Select count(quoteid) as TotalQuote, sum(totalamount) as QuoteAmount from     dbo.QuoteBase where CreatedOn BETWEEN @SDate AND @EDate)
CROSS JOIN (select  count(salesorderid)as TotalOrders, sum(totalamount) as OrderAmount from dbo.SalesOrderBase Where CreatedOn BETWEEN @SDate AND @EDate)
CROSS JOIN (select count(opportunityid)as TotalSales  from dbo.OpportunityBase Where
CreatedOn BETWEEN @SDate AND @EDate)

OR easier,

just do a:

SELECT * FROM table1, table2, table3

where table1,2 and 3 are your selects.

Hope it helps

Upvotes: 1

Related Questions