alter    PROCEDURE upGetWarehouseItems
    @warehouse_name       varchar(50),
  @criteria_name     varchar(50),
  @status_name       varchar(50),
  @customer_id       varchar(50),
  @warehouse_rpt     varchar(20),
  @track_num         varchar(50),
  @weight            varchar(50),
  @description       varchar(50),
  @bwtdate1          varchar(50),
  @bwtdate2          varchar(50)

AS

--Set Quoted_identifier off

DECLARE @ret bigint
Declare @sqlString  varchar(8000)
Select @sqlString = "
Select Distinct
       dk.drnumber,
       dk.date_received,
       lu.gross_weight,
       lu.sscc_number,
       lu.commodity_type,
       lu.w_location_id,
       lu.d_location_id,
       pd.length,
       pd.height,
       pd.width,
       pd.package_type,
       rTrim(lTrim(dk.cust_id)) cust_id,
       s.supplier_name,
       st.status,
       so.so_date,
       so.so_id,
       so.route_id,
       sm.shipping_type,
       wl.warehouse_area,
       wl.warehouse_aisle,
       wl.warehouse_tier,
       wl.warehouse_bin,
       lu.pkg_description,
       lu.handling,
       Case when Exists(Select * From lottransactions lt Where
lt.package_lot_id = pc.package_lot_id And emailSentFlag = 1) Then 1
Else 0 End emailSentFlag,
       lu.package_dimension_id,
       lu.barcode,
       lu.physical_status,
       lu.invoice,
       lu.declared_value,
       m.supplier_id,
       m.country_of_origin,
       m.qty_material_received,
       m.material_lot_number,
       m.supplier_lot_number,
       wh.warehouse_id,
       hb.house_bl_number,
       st.promised_date
JOIN   dockreceipt dk ON dk.warehouse_id = wh.warehouse_id
JOIN   housebl hb ON hb.house_bl_id = dk.house_bl_id
JOIN   logisticunit lu ON lu.dock_receipt_id = dk.drnumber
JOIN   warehouselocation wl ON wl.warehouse_location_id =
lu.w_location_id
JOIN   packagedimension pd ON pd.pack_dim_id = lu.package_dimension_id
JOIN   packagelot pc ON pc.sscc_number    = lu.sscc_number
JOIN   materiallot m ON m.package_lot_id = pc.package_lot_id
LEFT   JOIN supplier s ON s.supplier_id = m.supplier_id
Left   JOIN solineitem st ON st.so_line_item_id = lu.so_line_item_id
LEFT   JOIN serviceorders so ON so.so_id = st.so_id
LEFT   JOIN shippingmode sm ON sm.ship_mode_id = so.shipping_mode_id
WHERE    wh.warehouse_name    = '" + ltrim(rtrim(@warehouse_name)) + "'"

If (@criteria_name = 'S')
  Begin
    If(@status_name = 'No Service Order Created')
      Begin
      Select @sqlString = rTrim(@sqlString) + " AND st.status IS NULL"
      End
    Else
      Select @sqlString = rTrim(@sqlString) + " AND
rTrim(lTrim(st.status)) = '" + ltrim(rtrim(@status_name)) + "'"
  End

If (@criteria_name = 'C')
  Begin
      If(@customer_id <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
rTrim(lTrim(dk.cust_id)) = " + @customer_id
        End

      If(@track_num <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
rTrim(lTrim(lu.sscc_number)) = " + @track_num
        End

      If(@warehouse_rpt <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
rTrim(lTrim(hb.house_bl_number)) = " + @warehouse_rpt
        End

      If(@weight <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
CAST(ROUND(rTrim(lTrim(lu.gross_weight)),0) as varchar(20)) = " +
@weight
        End

      If(@description <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
rTrim(lTrim(lu.commodity_type)) Like ('%" + ltrim(rtrim(@description))
+ "%')"
        End

      If(@bwtdate1 <> '' AND @bwtdate2 <> '')
        Begin
        Select @sqlString = rTrim(@sqlString) + " AND
convert(varchar(10),dk.date_received,101) Between '" +
ltrim(rtrim(@bwtdate1)) + "' AND '" + ltrim(rtrim(@bwtdate2)) + "'"
        End
   End

Select @sqlString = rTrim(@sqlString) + "
AND lu.w_location_id    <> 0
ORDER  BY  dk.cust_id, st.status DESC, dk.date_received DESC "

Exec (@sqlString)

that is my sp

--upGetWarehouseItems 'Onebin-Miami', 'C', 'Waiting to be Processed',
'', '1312321', '', '', '', '', ''
that is my test

this works for all parameter expect the "warehouse_rpt"
when i try to query with the warehouse_rpt it gives me this

The conversion of the varchar value '12122222111' overflowed an int
column. Maximum integer value exceeded.

all paramerters are varchar and the hb.house_bl_number in the database
is a varchar(20) so i cant see why its giving me that error

Ian

I@n wrote:
Assuming house_bl_number is the column containing that large value, I
suspect the problem is here:

JOIN   housebl hb ON hb.house_bl_id = dk.house_bl_id

Is dk.house_bl_id also varchar(20)?

look at these 2 queries
select convert(int, '12122222111' )
select convert(bigint, '12122222111' )

the first one will fail because int is not big enough to hold the value
change your datatype to bigint to accomodate for bigger values



http://sqlservercode.blogspot.com/

On 27 Jan 2006 07:27:04 -0800, I@n wrote:

(snip)
Hi Ian,

The best way to troublshoot dynamic SQL (after avoiding it at all <g>)
is to print the SQL before executing it:

  PRINT @sqlString
  Exec (@sqlString)

If you'd do that, I bet that you'd see this in the SQL:

  AND rTrim(lTrim(hb.house_bl_number)) = 1312321

I don't know the datatype of hb.house_bl_number, but after the
rtrim(ltrim()) treatment, it'll definitely be character. And the
datatype of 1312321 is definitely integer. Since integer has the higher
precedence, SQL Server will cast rTrim(lTrim(hb.house_bl_number)) to
integer as well - and that fails for the character value '12122222111'
because it exceeds the maximum integer value.

I recommend that you read the following article by Erland Sommarskog:
http://www.sommarskog.se/dyn-search.html.

How to SELECT data among 4 tables?
Help with constraint
Table design (search with ranking)
How does engine decide whether to scan or seek index
Nightmare Query