mysql stored procedure recieving cannot be null errors from valid query

Go To StackoverFlow.com

0

Here is my stored Procedure

CREATE PROCEDURE `tblCarGarage_Insert`(
   IN URL     varchar(700),
   IN Price   int(7),
   IN Make              varchar(60),
   IN Model             varchar(60),
   IN TrimLevel         varchar(60),
   IN Miles             int(6),
   IN ZipCode           int(5),
   IN Description       varchar(80),
   IN Color             varchar(30),
   IN Owner             varchar(100),
   IN VIN               varchar(20),
   IN VINKeyCode        varchar(12),
   IN CarYear           int(4),
   IN ListingID         int(14))
INSERT INTO tblcargarage
      SET URL = @URL,
          Price = @Price,
          rBit = b'1',
          DateDiscovered = NOW(),
          LastProcessDate = NOW(),
          CarYear = @CarYear,
          Make =
             (SELECT uniqueID
                FROM tbltranslationmake
               WHERE make = 'Honda'),
          Model =
             (SELECT uniqueID
                FROM tbltranslationmodel
               WHERE model = @Model),
          CarTrim =
             (SELECT uniqueID
                FROM tbltranslationtrim
               WHERE trimlevel = @TrimLevel),
          Miles = @Miles,
          ZipCode = @ZipCode,
          Description = @Description,
          Color = @Color,
          Owner = @Owner,
          VIN = @VIN,
          VINKeyCode = @VinkeyCode,
          ListingID = @ListingID;

Here is my execution

SET @CarYear = '';
SET @URL = 'asdas';
SET @Price = 20112;
SET @Make = 'Honda';
SET @Model = 'Civic';
SET @TrimLevel = 'Ex';
SET @Miles = 20112;
SET @Description = 'asdasdasd';
SET @Color = 'yellow';
SET @Owner = 'asdasdas';
SET @VIN = 'qeqweqweqw2e';
SET @VinkeyCode = 'asd23sd';
SET @ListingID = 1231231;
SET @ZipCode = 12331;
CALL tblCarGarage_Insert(@CarYear,
                                              @URL,
                                              @Price,
                                              @Make,
                                              @Model,
                                              @TrimLevel,
                                              @Miles,
                                              @Description,
                                              @Color,
                                              @Owner,
                                              @VIN,
                                              @VinkeyCode,
                                              @ListingID,
                                              @ZipCode)

SELECT uniqueID
                FROM tbltranslationmake
               WHERE make = 'Honda'
returns 11 (as an int)

Model =
             (SELECT uniqueID
                FROM tbltranslationmodel
               WHERE model = @Model),

Returns 712 as an int

          CarTrim =
             (SELECT uniqueID
                FROM tbltranslationtrim
               WHERE trimlevel = @TrimLevel),
returns 12334 as an int.

Yes all 3 of those columns are INT in my DB,

I keep getting 'Column 'Make' cannot be null, if i run the query as a non SP just as a query Insert into asd set x = 'x' ect... it works just fine with the same values. Any idea?

Thanks!

This must have to do something with passing in a varchar and the table expecting an int. Maybe it's not returning the correct error?

2012-04-04 19:04
by NoName


0

Looking at your code, the first problem I notice is that in your CALL statement you are passing the parameters in the wrong order. For example, in the stored procedure definition the first parameter is URL, but in your call to the procedure the first parameter you pass in is CarYear.

Try passing the parameters in the correct order and see if that works for you:

CALL tblCarGarage_Insert(
  @URL,
  @Price,
  @Make,
  @Model,
  @TrimLevel,
  @Miles,
  @ZipCode,
  @Description,
  @Color,
  @Owner,
  @VIN,
  @VinkeyCode,
  @CarYear,
  @ListingID
)
2012-04-04 20:36
by Ike Walker
I think that might be the answer now sure why i get subquery returns more than 1 row consider they are all uniqued indexe - NoName 2012-04-04 21:00
Ads