CODEWITHSUNDEEP
sqlsql-server
andrew197
andrew197

Reputation: 27

Stored procedure not displaying error message

I am trying to write a stored procedure that accepts a carriers first name, last name and phone number but will return an error message if the name is already in the carrier table.

For some reason the error message doesn't show up when I add in a name that is already there. Not sure how to fix this

CREATE PROCEDURE AddCarrier
    (@firstname VARCHAR(30),
     @lastname VARCHAR(30), 
     @Phone CHAR(10))
AS
    IF EXISTS (SELECT firstname, lastname, phone FROM carrier)
    BEGIN
        RAISERROR('There already is a firstname and lastname of that value', 16, 1)
    END
    ELSE
    BEGIN
        INSERT INTO Carrier (FirstName, LastName, Phone)
        VALUES (@firstname, @lastname, @Phone)
   END
GO

Upvotes: 0

Views: 565

Answers (2)

Mohammad Neamul Islam
Mohammad Neamul Islam

Reputation: 283

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need a WHERE clause:

Create Procedure AddCarrier (
    @firstname varchar(30),
    @lastname varchar(30),
    @Phone char(10)
) as
BEGIN    
    IF EXISTS (Select firstname,lastname, phone
               from carrier c
               where c.firstname = @firstname and
                     c.lastname = @lastname and
                     c.phone = @phone)
       BEGIN
      RaisError('There is already a firstname and lastname of that value',16,1)
       END;
    ELSE
    Begin
       insert into Carrier (FirstName,LastName, Phone)
           values (@firstname, @lastname, @Phone)
    END;
END;

However, this is the wrong approach. Instead create a unique index and catch the exception:

create unique index unq_carrier_3 on carrier(firstname, lastname, phone);

Then the body should use an try/catch block:

Create Procedure AddCarrier (
    @firstname varchar(30),
    @lastname varchar(30),
    @Phone char(10)
) as
BEGIN    
    BEGIN TRY
       insert into Carrier (FirstName,LastName, Phone)
           values (@firstname, @lastname, @Phone);
    END TRY
    BEGIN CATCH  -- you can check for the particular error here
        RaisError('There is already a firstname and lastname of that value',16,1)
    END CATCH;
END;

The reason this is better is because the database validates the data integrity -- so you know the data is correct. In your version, race conditions could actually cause two rows with the same values to be inserted.

Upvotes: 2

Related Questions