CODEWITHSUNDEEP
sqlsql-server
Arash Mitooie
Arash Mitooie

Reputation: 1

Problem using a function that returns int

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

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

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

Upvotes: 1

Related Questions