Click here to Skip to main content
15,888,286 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE spInterventionValues
(
    @TableVar ILVariable1 READONLY,
    @NetworkName nvarchar(100)
)
AS
BEGIN
    SET NOCOUNT ON;
   
       
      select obj2.RoadID from tblNetwork obj1  join
       tblRoad obj2 on obj1.NetworkID=obj2.NetworkID 
  where obj1.Name=@NetworkName and obj2.SectionReference IN(SELECT SectionReference From @TableVar)
     
      select XSPID from tblXSP where Name IN(SELECT Name From @TableVar)
	  
      select Distance,Value from @TableVar 
      
    
 
    --WHERE ID IN (SELECT ID FROM @TableVar)
        
END
GO


This is my stored procedure...
I need to combine select statement into a single result set or single select statement
Posted
Comments
Tomas Takac 18-Dec-15 3:07am    
How would that single resultset look like? Could you post an example?
mithun286 18-Dec-15 4:38am    
RoadID XSPID Distance Value
------ ----- ------- -------
U4715/11 123 10 20

Like this
John C Rayan 18-Dec-15 6:42am    
Hi

Start with this. Let me have your table structures and data before finalizing the solution. Without data, I can't be sure if this will solve yours

select obj2.RoadID , tx.XSPID, tv.Distance,tv.Value
from @TableVar tv
left join tblRoad obj2 on obj2.SectionReference = tv.SectionReference
left join tblNetwork obj1 on obj1.NetworkID=obj2.NetworkID and obj1.Name = @NetworkName
left join tblXSP tx on tx.Name = tv.Name

If you want to get result based on 3 tables then use Joins[^]

If you want to combine the 3 select statement result into single, then you must remember one thing fields should be equal, if you want to know more about this refer below link Union[^]
 
Share this answer
 
I think what you need is UNION (Transact-SQL)[^] statement.

BUT: all your queries must select the same number of columns ; I can see that your third SELECT query is fetching two columns, whereas both first ones only fetch one.

Hope this helps.
 
Share this answer
 
Comments
John C Rayan 18-Dec-15 6:43am    
Hi Phil , I don't think Union does solve in this instance because the columns are not same. They are different from different tables.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900