0

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
zealous
  • 7,336
  • 4
  • 16
  • 36
Arash Mitooie
  • 1
  • 1
  • 2
    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 Jul 16 '20 at 15:40

1 Answers1

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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? – Arash Mitooie Jul 16 '20 at 15:58
  • 1
    @Arash No they are not strictly necessary _today_, but they may be someday, so it is just a good idea. [See this post](https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons). Also re: `dbo.` prefix see [this one](https://sqlblog.org/2019/09/12/bad-habits-to-kick-avoiding-the-schema-prefix). What does "doesn't work the way you've written it" mean? – Aaron Bertrand Jul 16 '20 at 16:01
  • @ArashMitooie . . . That is because functions have to include the schema name in SQL Server. – Gordon Linoff Jul 16 '20 at 16:01
  • actually you *can* execute functions, and in the case of `exec` the schema is not required. I don't recommend that though. https://stackoverflow.com/questions/5207036/is-there-a-way-to-use-a-function-on-a-microsoft-sql-server-query-without-using/5207124#5207124 – Martin Smith Jul 16 '20 at 16:07
  • @Gordon, @Aron Thanks guys. A `dbo.` before function name solved it. – Arash Mitooie Jul 16 '20 at 16:25