I am a beginner and need help: I have to convert my SQL query into a stored proc

ID: 3904783 • Letter: I

Question

I am a beginner and need help: I have to convert my SQL query into a stored procedure. this is my original query :

SELECT TM.MaintenanceID, TM.TruckNumber, TI.TruckDescription, TI.TruckCurrentMileage, LUMT.MaintenanceType, LUTT.TruckType, TM.MaintenanceStartDate, TM.MaintenanceEndDate, LUTB.TruckBodyType FROM TruckMaintenance AS TM

JOIN dbo.TruckInformation AS TI on TI.TruckNumber = TM.TruckNumber

JOIN dbo.LUTruckType AS LUTT ON LUTT.TruckTypeID = TI.TruckTypeID

JOIN dbo.LUMaintenanceType AS LUMT ON LUMT.MaintenanceTypeID = TM.MaintenanceTypeID

JOIN dbo.LUTruckBodyType AS LUTB ON LUTB.TruckBodyTypeID = TI.TruckBodyTypeID

WHERE MaintenanceStartDate BETWEEN '2018-01-01' AND '2018-12-31'

ORDER BY LUTT.TruckType ASC, TM.MaintenanceStartDate ASC.

This is my stored procedure that when i execute results in nothing

CREATE PROCEDURE [dbo].[TruckMaintenanceDates] (@MaintenanceStartDate date, @MaintenanceEndDate date)

BEGIN

SELECT TM.MaintenanceID,

TM.TruckNumber,

TI.TruckDescription,

TI.TruckCurrentMileage,

LUMT.MaintenanceType,

LUTT.TruckType,

TM.MaintenanceStartDate,

TM.MaintenanceEndDate,

LUTB.TruckBodyType

FROM TruckMaintenance AS TM

JOIN dbo.TruckInformation AS TI

on TI.TruckNumber = TM.TruckNumber

JOIN dbo.LUTruckType AS LUTT

ON LUTT.TruckTypeID = TI.TruckTypeID

JOIN dbo.LUMaintenanceType AS LUMT

ON LUMT.MaintenanceTypeID = TM.MaintenanceID

JOIN dbo.LUTruckBodyType AS LUTB

ON LUTB.TruckBodyTypeID = TI.TruckBodyTypeID

WHERE

MaintenanceStartDate BETWEEN @MaintenanceStartDate AND @MaintenanceEndDate

ORDER BY LUTT.TruckType ASC,

TM.MaintenanceStartDate ASC

SET NOCOUNT ON;

END

Explanation / Answer

Did it in Sql Server 2005 Management studio and now it is working.

Made some changes to your procedure

1) Removed the paranthesis as we need not specify input or output parameters of a procedure in paranthesis which defining the procedure

2) Changed the data type of @MaintenanceStartDate and @MaintenanceEndDate to datetime as SQL Server supports only datetime data type for date related columns

Below is the modified procedure

CREATE PROCEDURE [dbo].[TruckMaintenanceDates] @MaintenanceStartDate datetime, @MaintenanceEndDate datetime

BEGIN

SELECT TM.MaintenanceID,TM.TruckNumber,TI.TruckDescription,

TI.TruckCurrentMileage,LUMT.MaintenanceType,LUTT.TruckType,TM.MaintenanceStartDate,TM.MaintenanceEndDate,LUTB.TruckBodyType