SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 passing listbox values to query.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 09/22/2012 :  18:16:59  Show Profile  Reply with Quote
USE [ReplacementSchedule]
GO
/****** Object:  StoredProcedure [dbo].[GetReplacementStatusReport]    Script Date: 09/22/2012 12:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
          @snapShotID int,
          @bidOpStatusID  int,
          @fromType int,
          @toType int,
          @fromYear int,
          @toYear int,
          @divisionCode nvarchar(max),
          @deparmentCodes nvarchar(max),
          @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 = @bidOpStatusID) AND    
   (ReplacementSchedule.dbo.Units.Type BETWEEN @fromYear AND @toYear) 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  @fromType  AND @fromType) AND 
   (ReplacementSchedule.dbo.Units.ReplacementStatusID in (' + @repStatusIDs + ')) AND 
   (RTAFleet.dbo.vehfile.link_number = ReplacementSchedule.dbo.XrefNum.RTALinkNumber) AND 
   (ReplacementSchedule.dbo.Departments.DeptNumber = RTAFleet.dbo.vehfile.division AND 
   ((ReplacementSchedule.dbo.Departments.DeptCode IN (' + @deparmentCodes + ')) OR 
   (RTAFleet.dbo.vehfile.division = @divisionCode)
   )) ORDER BY VehicleNumber
END





Msg 245, Level 16, State 1, Procedure GetReplacementStatusReport, Line 20
Conversion failed when converting the varchar value ' + @repStatusIDs + ' to data type int.


Not sure what to do

Any help would be awesome.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/22/2012 :  19:04:50  Show Profile  Reply with Quote
The error is coming from the part shown below.
	       AND (
	               ReplacementSchedule.dbo.Units.ReplacementStatusID IN (' + @repStatusIDs + ')
	           )
As a quick fix, you can change that code to what I am showing next - but that may not be the real solution you want, because it is likely to be inefficient.
	       AND (
	               ','+ @repStatusIDs +',' LIKE
	               '%,'+CAST(ReplacementSchedule.dbo.Units.ReplacementStatusID AS VARCHAR(256)) + ',%' 
	           )
You will need to do a similar change the @departmentcodes as well.

Edited by - sunitabeck on 09/22/2012 19:05:07
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/22/2012 :  23:12:37  Show Profile  Reply with Quote
Run that query from an SSMS query window and see if you get any output. If you don't, remove the where conditions one at a time to see what is causing the results to be filtered out.
Go to Top of Page

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 09/23/2012 :  01:08:33  Show Profile  Reply with Quote
I made an error, I am going to try it again.
Can you please verify
<code>
ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
@snapShotID int,
@bidOpStatusID int,
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCode varchar(500),
@deparmentCodes 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 = @bidOpStatusID) 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
(((','+ @deparmentCodes +',' LIKE '%,'+CAST(ReplacementSchedule.dbo.Departments.DeptCode AS VARCHAR(256)) + ',%')
OR (RTAFleet.dbo.vehfile.division = @divisionCode))) ORDER BY VehicleNumber
END
<\code>

Any help would be awesome.
Go to Top of Page

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 09/23/2012 :  01:35:49  Show Profile  Reply with Quote
<code>
USE [ReplacementSchedule]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetReplacementStatusReport]
@snapShotID = 1,
@bidOpStatusID = 1,
@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',
@deparmentCode = 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,
@bidOpStatusID int,
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCodes varchar(500),
@deparmentCode 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 = @bidOpStatusID) 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 = @deparmentCode)) ORDER BY VehicleNumber
END
</code>
thank you!!!!

Any help would be awesome.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/23/2012 :  10:38:46  Show Profile  Reply with Quote
Since I (or anyone else who may want to respond) don't have the data and the tables that you are querying against, there isn't a way to figure out why you are getting no rows when you run the query. You will have to debug it, and one way to debug it is to alter the stored procedure by removing one where clause at a time to see which one is causing the elimination of the rows from the result set.

I am surprised that in your statement where you are calling the stored procedure, the PWSMT that I have marked in red below isn't generating an error when you run it.

     @deparmentCode = PWSMT,
     @repStatusIDs = N'1,2,3,4,5,6,7,8,9,10,11,12'

Go to Top of Page

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 09/24/2012 :  17:39:19  Show Profile  Reply with Quote
hi sunitabeck I posted another question,

Any help would be awesome.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/24/2012 :  18:50:30  Show Profile  Reply with Quote
What version of SQL are you using? You should really be using Table-Valued Parameters for this sort of thing.

http://msdn.microsoft.com/en-us/library/bb510489.aspx
http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000