SQL Server Stored Procedure Check if Record Exists Before Insert

Question

I am doing a check to see if a record exists before inserting it into a table (and this method seems to work on other stored procedures I am already using) but for this particular stored procedure it is not inserting anything even though the table is empty, why not?

CREATE PROCEDURE spInsertMovieHasTrailer
    @movieID int,
    @name varchar(50)
AS
BEGIN
    SELECT @name = name, @movieID = movieID
    FROM MovieHasTrailer
    WHERE name = @name and movieID = @movieID

    IF @name IS NULL and @movieID IS NULL
    BEGIN
    INSERT INTO MovieHasTrailer
    (
        movieID,
        name
    )
    Values (
        @movieID,
        @name
    )
    END
END

Executing like this:

execute spInsertMovieHasTrailer 1, 'Test'

Answer 1

I would build this directly into the insert and not use if logic. if introduces race conditions:

INSERT INTO MovieHasTrailer
    SELECT movieID, name
    FROM (SELECT @movieID as movieID, @name as name) t
    WHERE NOT EXISTS (SELECT 1
                      FROM MovieHasTrailer mht
                      WHERE mht.MovieId = t.MovieID AND mht.name = t.name
                     );

Note that this assumes that you need both the id and name to match the movie. I would think the id would be sufficient.

Also, what I would really do is have a unique index on either MovieHasTrailer(MovieId) or MovieHasTrailer(MovieId, Name). Then use a try/catch block if there is an insert error.

Answer 2

your select into variable may returns more than one value and you get error, it's better to use if not exists:

IF NOT EXISTS 
(
   SELECT name, movieID
   FROM MovieHasTrailer
   WHERE name = @name and movieID = @movieID
)
BEGIN
INSERT INTO MovieHasTrailer
(
    movieID,
    name
)
Values (
    @movieID,
    @name
)
END

Answer 3

The reason you are not doing an insert is the following code will not change the value of @name and @movieID if the query returns no records

SELECT @name = name, @movieID = movieID
FROM MovieHasTrailer
WHERE name = @name and movieID = @movieID

Whatever value for @name and @movieID you are passing into the stored procedure remain unchanged. I assume you are not passing in null values so the IF block is never executed.

Answer 4

You can try this way also you can achieve your goal and it save your time also.

INSERT INTO MovieHasTrailer
      SELECT @movieID as movieID, @name as name
        except
      select MovieId, name
        FROM MovieHasTrailer mht
              where  MovieId = @MoveID 

Answer 5

I would do this by standart MERGE statement:

Create table t(id int, name nvarchar(max))
Declare @id int = 1, @name nvarchar(max) = 'Mission imposible'

Merge t using (select @id, @name) as s(id, name)
on t.id = s.id
when not matched then
insert(id, name) values(s.id, s.name);

You can also add WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED BY SOURCE THEN DELETE to this statement.

Fiddle: http://sqlfiddle.com/#!6/c2569/23

Answer 6

try this :

CREATE PROCEDURE spInsertMovieHasTrailer
@movieID int,
@name varchar(50)
AS
BEGIN
declare @rowStatus nvarchar(50)=null

set @rowStatus=(SELECT name FROM MovieHasTrailer WHERE name = @name and movieID = @movieID)

IF (@rowStatus is NULL)
BEGIN
INSERT INTO MovieHasTrailer
(
    movieID,
    name
)
Values (
    @movieID,
    @name
)
END
END