USE [VP]
GO
/****** Object:  StoredProcedure [ML2].[GetVPInfos_Testing2]    Script Date: 03/26/2012 13:32:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ML2].[GetVPInfos_Testing]
	@start bigint,
	@range integer,
	@modulo integer,
	@type nvarchar(50),
	@subtype nvarchar(50),
	@pkgname nvarchar(100)
AS
BEGIN
	SELECT  DISTINCT
		q.company_id  AS VendorPartId, 
		q.manufacturer as Manufacturer, 
		q.manuf_pn as ManufacturerPartNumber, 
		t.main as MainType,
		t.sub as SubType,
		MAX(CASE WHEN p.parameter_id = 43 THEN p.display END) AS [PackageName], 
		MAX(CASE WHEN p.parameter_id = 36 THEN p.value END) AS [Depth], 
		MAX(CASE WHEN p.parameter_id = 37 THEN p.value END) AS [Height], 
	    MAX(CASE WHEN p.parameter_id = 38 THEN p.value END) AS [Diameter], 
	    MAX(CASE WHEN p.parameter_id = 39 THEN p.value END) AS [Length], 
	    MAX(CASE WHEN p.parameter_id = 17 THEN p.value END) AS ShapeType
    
	FROM    
		company.parameter AS p 
		join company.parts AS q ON p.company_id = q.company_id
		join company.types AS t ON t.type_id = q.company_type

	WHERE 
		q.transim_id  > @start
		and
		q.transim_id % @modulo = 0
		and
		t.main like @type
		and
		t.sub like @subtype
		and                         /**** problematic line 1 *****/
		p.display like @pkgname     /**** problematic line 2 *****/
		and
		q.company_id = 99645  /**** this is just for debugging purposes, will be removed in the final version ***/
	
	GROUP BY q.company_id, q.manufacturer, q.manuf_pn, q.company_type, t.main, t.sub
	ORDER BY t.main asc;
END

USE [VP]
GO

DECLARE	@return_value int
EXEC	@return_value = [ML2].[GetVPInfos_Testing]
		@start = 0,
		@range = 2000000,
		@modulo = 1,
		@type = N'%',
		@subtype = N'%',
		@pkgname = N'0805'
SELECT	'Return Value' = @return_value
GO

VendorPartId Manufacturer ManufacturerPartNumber MainType  SubType PackageName Depth Height Diameter Length ShapeType
99645        Manuf1        PartNumber1           Capacitor Ceramic 0805        NULL  NULL   NULL     NULL   NULL

VendorPartId Manufacturer ManufacturerPartNumber MainType  SubType PackageName Depth   Height Diameter Length ShapeType
99645        Manuf1        PartNumber1           Capacitor Ceramic 0805        0.00125 0.0022 NULL     0.002  NULL