Create a scalar function to return INT or 0

Question

0.00/5 (No votes)

See more:

I want to create a function to select an int (number of copies of a single movie) by taking the ID of the movie as a parameter and it returns 0 if the movie could not be found.

I created the query to find the number of copies but it doesn't return the number expected.
Moreover, where should I set the function to return 0 when no movies are found?

What I have tried:

CREATE FUNCTION Movies.ReturnNumberOfCopies(@IDMovies INT)
RETURNS INT
AS
BEGIN
   DECLARE @NumberOfCopiesIfMovieExists INT;
   --DECLARE @IDMovies INT;

   SELECT @NumberOfCopiesIfMovieExists = Movis.NumberOfCopies
   FROM Movies
   --WHERE Movies.IDMovies IS NOT NULL
   
   RETURN @NumberOfCopiesIfMovieExists 
END

to call the function and print the output I passed 3 as IDMovie but the number of copies doesn't match with the expected one

SELECT Movies.ReturnNumberOfCopies(3)   --

Posted 6-Nov-20 3:11am

xhon

Updated 6-Nov-20 4:12am

v5

Add a Solution

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-06T03:27:00

Solution 1

SQL

CREATE FUNCTION Movies.ReturnNumberOfCopies(@IDMovies INT)
RETURNS INT
AS
BEGIN
   DECLARE @NumberOfCopiesIfMovieExists INT;

   SELECT @NumberOfCopiesIfMovieExists = Movis.NumberOfCopies
   FROM Movies
   WHERE Movies.IDMovies = @IDMovies;
   
   RETURN IsNull(@NumberOfCopiesIfMovieExists, 0);
END

Be aware that the performance of multi-statement user-defined functions is not great.

Posted 6-Nov-20 3:27am

Richard Deeming

Comments

Richard Deeming 6-Nov-20 9:46am

You're trying to create a function in the schema whose name doesn't exist. And if it does exist, it conflicts with the name of your table, which could cause problems.

Create your function in the same schema as your table:

CREATE FUNCTION dbo.ReturnNumberOfCopies(@IDMovies INT) ...

xhon 6-Nov-20 9:58am

I have a couple of doubts: when you wrote :
RETURN IsNull((@NumberOfCopiesIfMovieExists, 0)

is like to say :"return values by default AND return 0 if the NumberOfCopies is 0", right?

And what if I wanted to assign an alias to the output column?

Richard Deeming 6-Nov-20 10:00am

ISNULL (Transact-SQL) - SQL Server | Microsoft Docs[^]
If @NumberOfCopiesIfMovieExists is not NULL, that value will be returned. Otherwise, the function will return 0.

There is no "output column" to alias in a scalar UDF.

xhon 9-Nov-20 11:58am

Thank you very much! I was wondering why shall we use variables to set the returned value instead than writing the name of the column: if I substitute the parameter @NumberOfCopis this way: 'return Movie.NumberOfCopies' I'm told that the multi-part identifier 'Movie.NumberoOfCopies' could not be bound, what does it mean and why is so important to use parameters within a sql function? It depends on the necessity of keeping track of the object data in order to be able to output their value, right?

Richard Deeming 9-Nov-20 13:08pm

You can't reference a column name outside of a SELECT or UPDATE statement. Return SomeTableName.SomeColumnName is not valid SQL syntax.