asked on

Combining 2 SQL SP's

I need to combine the results from these two working SP's. Any suggestions?

CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
      SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2]
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
   AND  [Event].[2] = (SELECT MAX( [2] ) FROM [Event] WHERE [1] = [GPRSDevice].[GPRSDeviceHardwareID] ) 
       ORDER BY [Vehicle].[VehicleName]
END
GO
********************************************************************************************************************
 
CREATE PROCEDURE [dbo].[GetTotalHoursByGPRSDeviceHardwareID]
(
     @GPRSDeviceHardwareID As varchar(50) 
)
AS
 
DECLARE @X As Int
DECLARE @Y As Int
DECLARE @Z As Int
 
 
BEGIN
 
		SET @X = 0
 
		IF EXISTS (SELECT 1 FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
		SET @X = (SELECT SUM(LineDifference) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
		
		SET @Y = 0
 
		IF EXISTS (SELECT 1 FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
		SET @Y = (SELECT SUM(LineDifference) FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
		
		SET @Z = 0
 
		IF EXISTS (SELECT 1 FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
		SET @Z =(SELECT HourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
RETURN @X + @Y + @Z
 
END
GO

Open in new window

Aneesh

CREATE PROCEDURE [dbo].[GetTotalHoursByGPRSDeviceHardwareID_new]
(
@GPRSDeviceHardwareID As varchar(50)
)
AS

DECLARE @X As Int
DECLARE @Y As Int
DECLARE @Z As Int

BEGIN

                  SELECT
                        [GPRSDevice].[GPRSDeviceHardwareID],
                        [Vehicle].[VehicleName],
                        [Event].[4],
                        [Event].[3],
                        [Event].[2]
                  FROM [GPRSDevice], [Vehicle], [Event]
                  WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
                        AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
                  AND [Event].[2] = (SELECT MAX( [2] ) FROM [Event] WHERE [1] = [GPRSDevice].[GPRSDeviceHardwareID] )
                  AND [GPRSDevice].[GPRSDeviceHardwareID] = @GPRSDeviceHardwareID ------------- you can comment out this
                  ORDER BY [Vehicle].[VehicleName]

SET @X = 0

IF EXISTS (SELECT 1 FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @X = (SELECT SUM(LineDifference) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @Y = 0

IF EXISTS (SELECT 1 FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @Y = (SELECT SUM(LineDifference) FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @Z = 0

IF EXISTS (SELECT 1 FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @Z =(SELECT HourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

RETURN @X + @Y + @Z

END
GO

MBoy

ASKER

It didn't work --

Msg 111, Level 15, State 1, Procedure AllLocationsWithVehicleNameAndTotalHoursByGPRSDeviceHardwareID, Line 17
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 178, Level 15, State 1, Procedure AllLocationsWithVehicleNameAndTotalHoursByGPRSDeviceHardwareID, Line 57
A RETURN statement with a return value cannot be used in this context.

Aneesh

how did u try that, try it on a new window

MBoy

ASKER

Sorry -

One problem exists. I need the Return @x + @y + @z number for each GPRSDeviceHardwareID. Your SP returns the sum of all GPRSDeviceHardwareID.

Aneesh

so, is "GPRSDeviceHardwareID" still an input parameter ?

MBoy

ASKER

No - I should have made my question more clear. Sorry

I need to return the following:

[GPRSDeviceHardwareID],
[Vehicle].[VehicleName],
[Event].[4],
[Event].[3],
[Event].[2]
@x + @y + @z

Sharath S

The why don't you try like this?

CREATE PROCEDURE [dbo].[GetTotalHoursByGPRSDeviceHardwareID_new]
(
     @GPRSDeviceHardwareID As varchar(50)
)
AS
 
DECLARE @X As Int
DECLARE @Y As Int
DECLARE @Z As Int
 
 
BEGIN
 
            SET @X = 0
 
            IF EXISTS (SELECT 1 FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
            SET @X = (SELECT SUM(LineDifference) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
           
            SET @Y = 0
 
            IF EXISTS (SELECT 1 FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
            SET @Y = (SELECT SUM(LineDifference) FROM HourMeterHistory WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
           
            SET @Z = 0
 
            IF EXISTS (SELECT 1 FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
 
            SET @Z =(SELECT HourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
            
            SELECT
	                            [GPRSDevice].[GPRSDeviceHardwareID],
	                            [Vehicle].[VehicleName],
	                            [Event].[4],
	                            [Event].[3],
	                            [Event].[2], 
	                            @X+@Y+@Z
	                      FROM  [GPRSDevice], [Vehicle], [Event]
	                      WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
	                            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
	                      AND  [Event].[2] = (SELECT MAX( [2] ) FROM [Event] WHERE [1] = [GPRSDevice].[GPRSDeviceHardwareID] )
	                      AND [GPRSDevice].[GPRSDeviceHardwareID] = @GPRSDeviceHardwareID  ------------- you can comment out this
                  ORDER BY [Vehicle].[VehicleName]
 
END
GO

Open in new window

ASKER CERTIFIED SOLUTION

wilje

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MBoy

ASKER

If this solution was an American Indian - it's name would be kickin' ass

wilje

You are welcome - glad I could help.