Stored procedures: the multi-part identifier could not be bound

Question

0.00/5 (No votes)

See more:

I want to create a stored procedure to insert or delete a movie in/from a table Movies.
The input parameters are Title and Director of the movie.
If the movie doesn't exist it will be created, otherwise I will increase the number of copies (the movie has an INT attribute 'numberOfCopies' to keep trace of it).
If I can't delete it the entire operation has to be cancelled.
I wrote the following batch code but when I run it I get the following error for each column:

the multi-part identifier Movies.NameOfColumn could not be bound

And also this one:

Invalid column name 'SCOPE:IDENTITY'

What I have tried:

CREATE PROCEDURE up_Movie_StoredProcedure 
@Title NVARCHAR(300),
@Director NVARCHAR(50)
@NumberOfCopies INT OUTPUT         -- parameters 
AS
-- Insert -->
IF  (
     Movie.Title != @Title
     AND Movie.Director != @Director
     	 )
    BEGIN  -- code block runs if condition is true
    INSERT INTO Movies(Title, Director)
	       VALUES (@Title, @Director );
		   SELECT SCOPE_IDENTITY;
    END 
ELSE
   BEGIN 
   UPDATE Movies
   SET Title = @Title,
       Director = @Director
       NumberOfCopies = NumberOfCopies + 1   -- output number of copies
   WHERE Title = @Title 
   OR   Director = @Director
   END
BEGIN
     SET @NumberOfCopies = (SELECT NumberOfCopies FROM Movies WHERE Title = @Title AND Dirctor = @Director);
   END

-- delete -->
BEGIN TRY
   BEGIN TRANSACTION
       DELETE 
	   FROM Movies
	   WHERE Title = @Title 
	     AND Director = @Director;
        DELETE r
		FROM Reservations r
		INNER JOIN Movies m ON m.IDMOvies = r.IDMovies
		WHERE m.Title = @Title
	      AND m.Director = @Director;
  COMMIT Transaction
END TRY
BEGIN CATCH
   ROLLBACK Transaction
END CATCH

Posted 9-Nov-20 0:29am

xhon

Updated 9-Nov-20 5:01am

v9

Add a Solution

Comments

[no name] 9-Nov-20 7:40am

Select count(*) for title and director. If the count is 0, you "insert" (based on your specs).

Richard Deeming 9-Nov-20 7:42am

Please stop using this UPSERT anti-pattern - SQLPerformance.com[^]

Run the UPDATE first. If no rows were affected, run the INSERT.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-11-09T01:42:00

There is nothing passed in to the procedure to indicate whether you want to insert/update the movie or delete the movie.

You should have two different procedures - one to insert or update:

SQL

CREATE PROCEDURE AddMovie
(
    @Title NVARCHAR(10), 
    @Director NVARCHAR(10)
)
As
BEGIN
    SET NOCOUNT ON;
    
    UPDATE
        Movies
    SET
        NumberOfCopies = NumberOfCopies + 1
    WHERE
        Title = @Title
    And
        Director = @Director
    ;
    
    If @@ROWCOUNT = 0
    BEGIN
        INSERT INTO Movies
        (
            Title,
            Director,
            NumberOfCopies
        )
        VALUES
        (
            @Title,
            @Director,
            1
        );
    END;
    
    SELECT
        NumberOfCopies
    FROM
        Movies
    WHERE
        Title = @Title
    And
        Director = @Director
    ;
END

and one to delete:

SQL

CREATE PROCEDURE RemoveMovie
(
    @Title NVARCHAR(10), 
    @Director NVARCHAR(10)
)
As
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY;
    BEGIN TRANSACTION;
        
        DELETE
        FROM R
        FROM Reservations As R
        INNER JOIN Movies As M ON M.IDMovie = R.IDMovie
        WHERE M.Title = @Title
        And M.Director = @Director;
        
        DELETE
        FROM Movies
        WHERE Title = @Title
        And Director = @Director;
        
        COMMIT;
    END TRY
    BEGIN CATCH;
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH;
END