November 21, 2012 at 4:03 am
Hi all,
how can i write 2 select statements in single procedure.
November 21, 2012 at 4:08 am
CREATE PROC dbo.usp_TwoSelects
AS
BEGIN
SELECT * FROM dbo.Table1
SELECT * FROM dbo.Table2
END
November 21, 2012 at 4:12 am
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
November 21, 2012 at 4:18 am
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;-)
November 21, 2012 at 4:24 am
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.
November 21, 2012 at 4:32 am
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;-)
November 21, 2012 at 4:36 am
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 ...
November 21, 2012 at 4:39 am
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
November 21, 2012 at 5:38 am
How do you execute you stored procedure?
Is it from SSMS or your client application or something else?
November 21, 2012 at 6:59 am
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
November 22, 2012 at 12:04 pm
...duplicate post
November 22, 2012 at 12:29 pm
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