Problem using a function that returns int

Question

Here's a very simple function to return student id by name.The function works but I have trouble getting use of it:

--function to find student id by name (works)
create function findStudent (@name nvarchar(15), @familyName nvarchar(15))
returns tinyint
as
begin
    declare @id tinyint
    select @id = (select teaID from Tea where teaFirstName = @name and teaLastName = @familyName)
    return @id
end;

--using the function (doesn't work)
declare @id tinyint
select @id = (execute findStudent 'Arash', 'Mitooie')
print @id

Where did you find example syntax that you execute a function or that you pass arguments without parens? Try SELECT @id = dbo.findStudent(N'Arash'. N'Mitooie'); — Aaron Bertrand
– Aaron Bertrand, Commented Jul 16, 2020 at 15:40

Gordon Linoff · Accepted Answer · 2020-07-16 17:09:03Z

1

You have a function. No execute is necessary. Just plug it into a select:

declare @id tinyint;
select @id = dbo.findStudent('Arash', 'Mitooie');
print @id;

You can also define this as a stored procedure. In that case, I would advise passing the @id in as an OUTPUT parameter and assigning the value in the procedure.

You should also review the differences between stored procedures and stored functions, because you seem to be confusing the two. At the simplest, stored functions return either a single value or a result set and are intended to be used in queries. Stored procedures cannot be used in queries (with the exception of insert under some special circumstances that I don't recommend using).

edited Jul 16, 2020 at 17:09

answered Jul 16, 2020 at 15:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arash Mitooie Over a year ago

Thanks Gordon. I do intend to use it in queries, but unfortunately it doesn't work your way either . . . BTW: Are all those semicolons necessary in SQL?

Aaron Bertrand Over a year ago

@Arash No they are not strictly necessary today, but they may be someday, so it is just a good idea. See this post. Also re: dbo. prefix see this one. What does "doesn't work the way you've written it" mean?

Gordon Linoff Over a year ago

@ArashMitooie . . . That is because functions have to include the schema name in SQL Server.

Martin Smith Over a year ago

actually you can execute functions, and in the case of exec the schema is not required. I don't recommend that though. stackoverflow.com/questions/5207036/…

Arash Mitooie Over a year ago

@Gordon, @Aron Thanks guys. A dbo. before function name solved it.

Collectives™ on Stack Overflow

Problem using a function that returns int

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related