CODEWITHSUNDEEP
mysqlstored-procedures
Eternal Learner
Eternal Learner

Reputation: 3870

Doubt in Stored Procedure MySql - how to return multiple values for a variable?

I have a stored procedure below. I intend this procedure to return the names of all the movies acted by an actor.

 Create Procedure ActorMovies(
  In ScreenName varchar(50),
  OUT Title varchar(50)
  )
  BEGIN
  Select MovieTitle INTO Title  
   From Movies Natural Join Acts 
   where Acts.ScreenName = 'ScreenName ';
 End;

I make a call like Call ActorMovies(' Jhonny Depp',@movie);

Select @move;

The result I get is a Null set , which is not correct.I am expecting a set of movies acted by Jhonny Depp to be returned. I am not sure as to why this is happening?

Upvotes: 1

Views: 1448

Answers (2)

Sanjeev
Sanjeev

Reputation: 1

Select MovieTitle AS Title From Movies Natural Join Acts     where Acts.ScreenName = 'ScreenName ';

This should resolve the problem. INTO is used for OUT parameter when a single value is required. Here you are fetching a set.

Upvotes: 0

psmears
psmears

Reputation: 28000

In your where clause, you want to remove the single quotes round ScreenName (and possibly rename it to avoid the ambiguity with the column names).

Upvotes: 1

Related Questions