|
mcupryk
Yak Posting Veteran
Canada
83 Posts |
 Posted - 09/24/2012 : 17:43:02
|
<code>
USE [ReplacementSchedule]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[GetReplacementStatusReport]
@snapShotID = 1,
@bidOpStatusIDs = N'1,2,3,4,5,6,7,8,9,10,11,12',
@fromType = 101,
@toType = 999,
@fromYear = 1997,
@toYear = 2024,
@divisionCodes = N'PW14040403,PW14040405,PW14040406,PW17010905,PW17020903,PW17030906,PW17040902,PW17050101,PW17060901,PW17070907,PW17080907,PW17090907,PW17100907,PW17110907,PW17140902,PW17160901',
@departmentCode = PWSMT,
@repStatusIDs = N'1,2,3,4,5,6,7,8,9,10,11,12'
SELECT 'Return Value' = @return_value
GO
ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
@snapShotID int,
@bidOpStatusIDs varchar(500),
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCodes varchar(500),
@departmentCode varchar(500),
@repStatusIDs varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT VehicleNumber = CASE WHEN ISNUMERIC(RTAFleet.dbo.vehfile.vehicle) = 1 AND RTAFleet.dbo.vehfile.vehicle IS NOT NULL THEN
RTAFleet.dbo.vehfile.vehicle ELSE RTAFleet.dbo.vehfile.veh_xref_num END,
ReplacementSchedule.dbo.Units.LinkNumber AS RPLinkNumber,
RTAFleet.dbo.vehfile.link_number AS LinkNumber,
ReplacementSchedule.dbo.Departments.DeptDescription AS Department,
Model= CASE WHEN RTAFleet.dbo.vehfile.vehicle_model IS NULL OR
RTAFleet.dbo.vehfile.vehicle_model = ' ' THEN 'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_model END,
Make = CASE WHEN RTAFleet.dbo.vehfile.vehicle_make IS NULL OR RTAFleet.dbo.vehfile.vehicle_make = ' ' THEN
'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_make END,
RTAFleet.dbo.vehfile.vehicle_location AS ClassCode,
ReplacementSchedule.dbo.Units.BudgetYear AS BudgetYear,
ReplacementSchedule.dbo.Units.RevisedYear AS RevisedYear,
(SELECT ReplacementSchedule.dbo.Types.RepCost FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
AS RepCost,
(SELECT ReplacementSchedule.dbo.Types.TypeDescription FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
As TypeDes,
BidOpStatus = CASE WHEN ReplacementSchedule.dbo.BidOppStatus.BidOffStatu IS NULL THEN 'N/A'
ELSE ReplacementSchedule.dbo.BidOppStatus.BidOffStatu END FROM ReplacementSchedule.dbo.Departments,
ReplacementSchedule.dbo.Units INNER JOIN ReplacementSchedule.dbo.XrefNum ON
ReplacementSchedule.dbo.Units.LinkNumber = ReplacementSchedule.dbo.XrefNum.RPLinkNumber LEFT JOIN
ReplacementSchedule.dbo.BidOppStatus ON
ReplacementSchedule.dbo.Units.BidOpStatusID = ReplacementSchedule.dbo.BidOppStatus.BidOffStatuID,
RTAFleet.dbo.vehfile WHERE
(ReplacementSchedule.dbo.Units.BidOpStatusID = @bidOpStatusIDs) AND
(ReplacementSchedule.dbo.Units.Type BETWEEN @FromType AND @ToType) AND RTAFleet.dbo.vehfile.vehicle NOT LIKE 'JC%' AND
(ReplacementSchedule.dbo.Units.DisposalOrderID = 0 OR ReplacementSchedule.dbo.Units.DisposalOrderID IS NULL) AND
ReplacementSchedule.dbo.Units.SnapShotID = @SnapShotID AND (ReplacementSchedule.dbo.Units.RevisedYear BETWEEN @FromYear AND @ToYear) AND
(','+ @repStatusIDs +',' LIKE '%,'+ CAST(ReplacementSchedule.dbo.Units.ReplacementStatusID AS VARCHAR(256)) + ',%') AND
RTAFleet.dbo.vehfile.link_number = ReplacementSchedule.dbo.XrefNum.RTALinkNumber AND
ReplacementSchedule.dbo.Departments.DeptNumber = RTAFleet.dbo.vehfile.division AND
((',' + @divisionCodes + ',' LIKE '%,' + RTAFleet.dbo.vehfile.division + ',%')
OR (ReplacementSchedule.dbo.Departments.DeptCode = @departmentCode)) ORDER BY VehicleNumber
END
GO
value '1,2,3,4,5,6,7,8,9,10,11,12' to data type int.
</code>
I need to get @bidOpStatusIDs varchar(500), pass dropdownlist.
Any help would be awesome. |
Edited by - mcupryk on 09/24/2012 17:56:21
|
|
one more question
