CODEWITHSUNDEEP
sqlsql-serverselectexecprocedure
Oscar Larsson
Oscar Larsson

Reputation: 83

Combine two different Selects into same result table in my procedure?

I'm trying to write a procedure with two selects. Is there any way to combine two selects into same result table?

Right now I get results in two different tables.

My result right now

Here's my procedure

ALTER PROCEDURE [dbo].[GetMachineStatus] (@MachineID int) as
begin
    set nocount on
    declare @Availabilitytime datetime, @TimeNow datetime, @queryMachineStatus nvarchar(250)

    select
          @Availabilitytime =
                (select AvailabilityTime from machines where MachineID = @MachineID)
        , @TimeNow = getDate()
        , @queryMachineStatus = N'select avg(effectively) As Availability, (MAX(Counter)-MIN(counter)) As Counter from '
                + quoteName(convert(nvarchar, @MachineID))
                + N' where [time] between '
                + quoteName(convert(nvarchar, @Availabilitytime, 120), nchar(39))
                + N' and '
                + quoteName(convert(nvarchar, @TimeNow, 120), nchar(39))
                + N'select top (1) effectively as effectively from'
                + quoteName(convert(nvarchar, @MachineID))
                + N' Order by time DESC'
    
    execute sp_executesql @queryMachineStatus
    
End

Upvotes: 0

Views: 42

Answers (1)

Dale K
Dale K

Reputation: 27226

Assuming you want wanting to add an additional column, and assuming your first result is only ever one row, then a simple sub-query should do the job e.g.

set @queryMachineStatus = N'select avg(effectively) as Availability, (MAX(Counter)-MIN(counter)) as Counter'

+ N', (select top (1) effectively as effectively from'
+ quotename(convert(nvarchar(30), @MachineID))
+ N' Order by time DESC) as Effectively'

+ ' from '
+ quotename(convert(nvarchar(30), @MachineID))
+ N' where [time] between '
+ quotename(convert(nvarchar(30), @Availabilitytime, 120), nchar(39))
+ N' and '
+ quotename(convert(nvarchar(30), @TimeNow, 120), nchar(39));

Upvotes: 1

Related Questions