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?
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
)