how can i write 2 select statements in single procedure

  • Hi all,

    how can i write 2 select statements in single procedure.

  • CREATE PROC dbo.usp_TwoSelects

    AS

    BEGIN

    SELECT * FROM dbo.Table1

    SELECT * FROM dbo.Table2

    END

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi,

    thanks for reply.

    still it displays first select statement values. here is my code....

    ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]

    ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)

    as

    begin

    Select (cast(Max(D.Bowling_Wickets)as varchar)+'-'+cast(Min(D.Bowling_Runs)as varchar)) as Bestbowling

    From InningsBowlingDetails D

    JOIN (Select D.Bowling_MemberKey, Max(D.Bowling_Wickets) as Wickets from InningsBowlingDetails D group by Bowling_MemberKey) w

    ON D.Bowling_MemberKey=w.Bowling_MemberKey AND D.Bowling_Wickets=w.Wickets

    join tbl_Fixtures B on B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    join FixtureInnings C on C.Innings_FixtureKey=B.Fixture_Key and D.Bowling_InningsKey=C.Innings_Key

    group by D.Bowling_MemberKey

    select E.Member_FirstName as Player ,

    Count(A.FixturePlayer_MemberKey) As Matches ,

    SUM(D.Bowling_Overs) As Overs,

    SUM(D.Bowling_Maidens) as Maidens,

    SUM(D.Bowling_Runs) as Runs,

    SUM(D.Bowling_Wickets) as Wickets,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average

    from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E

    where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and E.Member_Key=D.Bowling_MemberKey

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    and D.Bowling_MemberKey=A.FixturePlayer_MemberKey

    and A.FixturePlayer_FixtureKey=B.Fixture_Key

    and C.Innings_FixtureKey=B.Fixture_Key

    and D.Bowling_InningsKey=C.Innings_Key

    Group By A.FixturePlayer_MemberKey,E.Member_FirstName

    end

  • below is the two sql i seperate .

    ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]

    ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)

    as

    begin

    Select (cast(Max(D.Bowling_Wickets)as varchar)+'-'+cast(Min(D.Bowling_Runs)as varchar)) as Bestbowling

    From InningsBowlingDetails D

    JOIN (Select D.Bowling_MemberKey, Max(D.Bowling_Wickets) as Wickets from InningsBowlingDetails D group by Bowling_MemberKey) w

    ON D.Bowling_MemberKey=w.Bowling_MemberKey AND D.Bowling_Wickets=w.Wickets

    join tbl_Fixtures B on B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    join FixtureInnings C on C.Innings_FixtureKey=B.Fixture_Key and D.Bowling_InningsKey=C.Innings_Key

    group by D.Bowling_MemberKey

    select E.Member_FirstName as Player ,

    Count(A.FixturePlayer_MemberKey) As Matches ,

    SUM(D.Bowling_Overs) As Overs,

    SUM(D.Bowling_Maidens) as Maidens,

    SUM(D.Bowling_Runs) as Runs,

    SUM(D.Bowling_Wickets) as Wickets,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average

    from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E

    where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and E.Member_Key=D.Bowling_MemberKey

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    and D.Bowling_MemberKey=A.FixturePlayer_MemberKey

    and A.FixturePlayer_FixtureKey=B.Fixture_Key

    and C.Innings_FixtureKey=B.Fixture_Key

    and D.Bowling_InningsKey=C.Innings_Key

    Group By A.FixturePlayer_MemberKey,E.Member_FirstName

    end

    Still it displays first select statement values

    Have you tested the second sql code (select query ) whether its returning the results or not ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • hi,

    its returning first select statement values.

    means if u changed the positions of select statements then also its displays

    select stmt values means which one first the values r getting from that query only.

  • sandeep.vemulas (11/21/2012)


    hi,

    its returning first select statement values.

    means if u changed the positions of select statements then also its displays

    select stmt values means which one first the values r getting from that query only.

    i didnt get your point here

    what you mean here ? that if you suffle column's position in select then you are getting the data as previous OR you want to say that you SECOND select in stored proc is not returning any data :unsure:

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi ,

    Procedure is working fine ...

    Create procedure dbo.SqlServerForum

    as

    Select Top 1 * from sys.tables

    Select Top 1 * from sys.columns

    Try this and work it out on this ...

  • Hi,

    if am execute this query am getting first select statement values.

    ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]

    ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)

    as

    begin

    Select (cast(Max(D.Bowling_Wickets)as varchar)+'-'+cast(Min(D.Bowling_Runs)as varchar)) as Bestbowling

    From InningsBowlingDetails D

    JOIN (Select D.Bowling_MemberKey, Max(D.Bowling_Wickets) as Wickets from InningsBowlingDetails D group by Bowling_MemberKey) w

    ON D.Bowling_MemberKey=w.Bowling_MemberKey AND D.Bowling_Wickets=w.Wickets

    join tbl_Fixtures B on B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    join FixtureInnings C on C.Innings_FixtureKey=B.Fixture_Key and D.Bowling_InningsKey=C.Innings_Key

    group by D.Bowling_MemberKey

    select E.Member_FirstName as Player ,

    Count(A.FixturePlayer_MemberKey) As Matches ,

    SUM(D.Bowling_Overs) As Overs,

    SUM(D.Bowling_Maidens) as Maidens,

    SUM(D.Bowling_Runs) as Runs,

    SUM(D.Bowling_Wickets) as Wickets,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average

    from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E

    where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and E.Member_Key=D.Bowling_MemberKey

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    and D.Bowling_MemberKey=A.FixturePlayer_MemberKey

    and A.FixturePlayer_FixtureKey=B.Fixture_Key

    and C.Innings_FixtureKey=B.Fixture_Key

    and D.Bowling_InningsKey=C.Innings_Key

    Group By A.FixturePlayer_MemberKey,E.Member_FirstName

    end

    [highlight=#ffff11]this is result[/highlight]

    2-2

    0-3

    2-45

    5-49

    0-0

    2-52

    0-0

    2-43

    1-32

    --------------------------------------------------------------

    if am executing this query

    ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]

    ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)

    as

    begin

    select E.Member_FirstName as Player ,

    Count(A.FixturePlayer_MemberKey) As Matches ,

    SUM(D.Bowling_Overs) As Overs,

    SUM(D.Bowling_Maidens) as Maidens,

    SUM(D.Bowling_Runs) as Runs,

    SUM(D.Bowling_Wickets) as Wickets,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,

    isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average

    from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E

    where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and E.Member_Key=D.Bowling_MemberKey

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    and D.Bowling_MemberKey=A.FixturePlayer_MemberKey

    and A.FixturePlayer_FixtureKey=B.Fixture_Key

    and C.Innings_FixtureKey=B.Fixture_Key

    and D.Bowling_InningsKey=C.Innings_Key

    Group By A.FixturePlayer_MemberKey,E.Member_FirstName

    Select (cast(Max(D.Bowling_Wickets)as varchar)+'-'+cast(Min(D.Bowling_Runs)as varchar)) as Bestbowling

    From InningsBowlingDetails D

    JOIN (Select D.Bowling_MemberKey, Max(D.Bowling_Wickets) as Wickets from InningsBowlingDetails D group by Bowling_MemberKey) w

    ON D.Bowling_MemberKey=w.Bowling_MemberKey AND D.Bowling_Wickets=w.Wickets

    join tbl_Fixtures B on B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END

    and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END

    and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END

    join FixtureInnings C on C.Innings_FixtureKey=B.Fixture_Key and D.Bowling_InningsKey=C.Innings_Key

    group by D.Bowling_MemberKey

    end

    [highlight=#ffff11]Am getting this result for above query[/highlight]

    venu1252463915

    Member31303010

    venu32821514537

    sandeep332216214511

    A4000000

    sa32731224430

    sa1231000000

    ds31941634840

  • How do you execute you stored procedure?

    Is it from SSMS or your client application or something else?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Your application needs to use the techniques for Multiple Active Result Sets (MARS);

    just dumping the resutls into a DataReader or DataTable will only get you one of potentially multiple result sets.

    here's a very basic example, using the Microsoft Application Blocks SQLHelper:

    Private Sub btnMars_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMars.Click

    Dim sql As String

    sql = "select top 1 * from sys.tables; select top 3 * from sys.objects;"

    Dim ds As New DataSet

    ds = SqlHelper.ExecuteDataset(Me.ConnectionString, CommandType.Text, sql)

    Dim i As Integer = 0

    Dim LastTop As Integer = 0

    For Each dt As DataTable In ds.Tables

    'do something witht eh results...stick them in a grid, or whatever

    'MsgBox(dt.Rows.Count)

    Next

    End Sub

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ...duplicate post

  • I'm sorry that I didn't wade through the details of your code. But if you need to run one compplex query that produces output that you want to use in a second complex query, I would break the process down into steps. There may be more elegant ways to do something like this in one large, complex query but it makes things difficult for you or someone else to come back a year or two later and figure out what you did.

    So I'd take each query and make two separate stored procedures out of them. Then create a third stored procedure that runs the first procedure and outputs the results to a temp table. Then query the temp table to get whatever outputs you need as inputs to the second procedure to create a second temp table. Then you can query either table or join them or whatever like any other set of tables or views.

    Below is some pseudo-code that should give you the gist of the process.

    CREATE PROCEDURE dbo.GetResultsFromTwoProcedures

    @spInput1 INT

    ,@spInput2 VARCHAR(50)

    AS

    BEGIN

    SET NOCOUNT ON

    CREATE TABLE #TempTable1

    [rest of create statement, etc]

    [cols must match the cols you are going to return with the first stored procedure]

    CREATE TABLE #TempTable2

    [rest of create statement, etc]

    [cols must match the cols you are going to return with the second stored procedure]

    DECLARE @spInput3 INT, @spInput4 VARCHAR(50)

    --put the results of the first procedure into the first temp table

    INSERT INTO #TempTable1

    EXEC dbo.StoredProcedure1 @spInput1, @spInput2

    --now select any values you need from the first procedure

    --and assign them to variables

    SELECT

    @spInput3 = [Col1]

    ,@spInput4 = [Col2]

    FROM #TempTable1

    WHERE [etc]

    --use the results of the first procedure as inputs for the second procedure

    INSERT INTO #TempTable2

    EXEC dbo.StoredProcedure2 @spInput3, @spInput4

    --finally you can display the results

    SELECT * FROM #TempTable2

    --or you can join them or do whatever filtering, etc you need

    SELECT

    *

    FROM

    #TempTable1 as 1

    INNER JOIN

    #TempTable2 as 2

    ON 1.PK = 2.PK

    WHERE [etc]

    END

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply