user defined functions returning multiple values in sql

Question

I have the following tables in sql :

I want to write an function that will take Student_ID as parameter and return the Student_Name and Balance from the above table.

How can I do that ?

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Apr 02 '16 at 17:17
@osimerpothe I have posted an answer for sql-server...Check whether it is correct or not.. — Shiv, Apr 02 '16 at 17:18

score 2 · Answer 1 · edited May 23 '17 at 12:10

2

CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc`(IN `id` INT)
NO SQL
SELECT student.Student_Name, account.Balance 
FROM student INNER JOIN account
ON student.Student_ID = account.Student_ID
WHERE student.Student_ID = id

you can create procedure from phpmyadmin: How to write a stored procedure in phpMyAdmin?

edited May 23 '17 at 12:10

Community

1
1

answered Apr 02 '16 at 17:42

Jahangir Alam

801
8
21

score 1 · Answer 2 · answered Apr 02 '16 at 17:14

Function can be created as:

CREATE FUNCTION function_name(@Student_ID int)
RETURNS @name_and_balanceTable TABLE 
(
    -- Columns returned by the function_name
    Name nvarchar(50),
    Balance int
)
AS 
-- Returns the Name and Balance for particular Student_ID
BEGIN
    -- Selecting the name and balance
    SELECT 
        @Name = st.Student_Name, 
        @Balance = ac.Balance
    FROM Student st JOIN Account ac ON st.Student_ID = ac.Student_ID 
    WHERE st.Student_ID = @Student_ID;

    -- Insert these value into table
    BEGIN
        INSERT @name_and_balanceTable
        SELECT @Name, @Balance;
    END;
    RETURN;
END;

This is for sql-server. For more information use this link...

user defined functions returning multiple values in sql

2 Answers2