SQL stored procedure error about int while there is no int

Question

In my stored procedure (insert stored procedure) I have a section that checks if the customer exists. When I give a customer name (varchar) that exists there are no errors but when I insert a Name that does not exists It gives me this error:

Conversion failed when converting the varchar value 'Briggs Standih' to data type int.

Briggs Standih is a non-existsing customer in my database.

I have no clue where the mistake could be... Here is my procedure and the executeable.

create PROCEDURE spNieuweReservering
( 
   @customer VARCHAR(255), 
   @date1 date,
   @date 2date,
   @hotel varchar(40),
   @count_person int ,
   @emplyeeidint,
   @roomidint

) 
AS 
BEGIN 
   DECLARE @bookdate date    
IF not exists (
   SELECT count(*)
   FROM customer
   WHERE @customer= name
   )   
   BEGIN 
    raiserror('Deze klant bestaat niet. Controleer de naam of maak een nieuwe klant aan.',16,1)
   END 

   select @customer= customerid
   from customer
   where @customer= name

   select @hotel = hotelid
   from hotel
   where @hotel = location

   SELECT @bookdate= GETDATE()
   FROM boeking 

   begin transaction
   INSERT INTO booking values (@bookdate, @count_person, null, @customer, @hotel, @employeeid)
   insert into roombooking values (@roomid, @date1, @date2, null, null)
IF @@ERROR <> 0
BEGIN
    rollback
    raiserror('Er is een fout opgetreden bij het toevoegen van de reservering. Controleer de input',16,1)
    return
END
END 
commit

exec spNieuweReservering 'Briggs Standih', '2019-08-10', '2019-08-15', 'Eindhoven', 1, 1, 1

Hopefully someone does know what I'm doing wrong.

Answer 1

The reason the code is getting to the point where you get the error is because your code to check for an existing customer has a logical problem. You're trying to ask whether a row exists containing that customer's name. However because you used COUNT(), what you're actually asking is whether there is a row containing the count of the number of customers with that name. Even if the count is 0, there will always be a row, so EXISTS will always be true.

IF not exists (
   SELECT name
   FROM customer
   WHERE @customer= name

will fix that, by trying to select a field rather than using an aggregate function.

---

Beyond that, the error itself stems from some poor programming practice. Specifically I'm talking about the use of the same variable to store two different pieces of information, and also not using the correct data types to store information.

You've done this twice, with both @customer and @hotel - they both start out representing names, but you later try to re-use them to represent an ID. This is bad because it's confusing to use the same thing for two purposes, and also because the IDs are numbers and ought to be stored in int variables. And of course when the error checking fails, and the non-existent customer name is passed into select @customer= customerid from customer where @customer= name, this does not select an ID, so @customer continues to contain the name. You then later try to insert this into the booking field, whose customer column is an int column - hence the error that you cannot insert a varchar (i.e. text) value into an integer field.

As good practice I'd recommend that you

a) create separate integer variables to represent the customer ID and hotel ID values.

b) explicitly state the columns in your INSERT statements. (This will save you later if you add or remove other columns in the target tables, then you won't have an issue with the order or number of the columns being wrong. It results in less code maintenance, plus increasing readability.)

c) As a broader recommendation, I suggest that your stored procedure actually accepts the customer ID and hotel ID as input parameters, instead of the name. There's always a danger that names will change, or even that two records could actually have the same name (e.g. in UK culture it would not be at all surprising to have two customers called John Smith), meaning you could select the wrong ID. I assume there is a user interface in front of your database. It should be possible in the UI for the user to select a customer - perhaps from a dropdown list or an autocomplete, and for the UI to know which customer ID this relates to. If it is a new customer, then the user must create a new customer record first before proceeding with the rest of the booking process. Most professionally developeed applications follow this approach (in respect to making selections from existing records). So after the user selects or creates a customer, the resulting customer ID is then given to the stored procedure, instead of a name. That way there is absolutely no room for any ambiguity about which customer is making the booking.

Answer 2

create PROCEDURE spNieuweReservering
( 
   @customer VARCHAR(255), 
   @date1 date,
   @date2 date,
   @hotel varchar(40),
   @count_person int ,
   @emplyeeid int,
   @roomid int

) 
AS 
BEGIN 
   DECLARE @bookdate date    
   SET NOCOUNT ON;
   begin transaction MainTrans
IF not exists (SELECT count(*)  FROM customer WHERE @customer= name ) BEGIN 
    raiserror('Deze klant bestaat niet. Controleer de naam of maak een nieuwe klant aan.',16,1)
END 
BEGIN
   select @customer= customerid
   from customer
   where @customer= name

   select @hotel = hotelid
   from hotel
   where @hotel = location

   SELECT @bookdate= GETDATE()
   FROM boeking 

   INSERT INTO booking values (@bookdate, @count_person, null, @customer, @hotel, @employeeid)
   insert into roombooking values (@roomid, @date1, @date2, null, null)
END
IF @@ERROR <> 0
BEGIN
    rollback TRANSACTION MainTrans
    raiserror('Er is een fout opgetreden bij het toevoegen van de reservering. Controleer de input',16,1)
    return
END
ELSE
          BEGIN
             COMMIT TRANSACTION MainTrans               
          END
END 

Answer 3

  select @customer= customerid
  from customer
  where @customer= name

This is your mistake you try to put an int into @customer which is varchar. You will have the same error with the hotel

select @hotel = hotelid
from hotel
where @hotel = location

I suggest declaring 2 other variables

DECLARE @hotelId, @customerId INT

And using those to achieve your result