CREATE PROC add_person
(
    @id tinyint,
    @name nvarchar(max),
    @surname nvarchar(max),
    @salary int,
    @job nvarchar(max)
)
AS
BEGIN
    INSERT INTO information
    VALUES(@id,@name,@surname,@salary,@job)
END

I want to write this code as a function. But the concept of "return" confuses me. That's why I couldn't.

I tried to write the code above as a function. This code came out.

CREATE FUNCTION add_person
(
    @id tinyint,
    @name nvarchar(max),
    @surname nvarchar(max),
    @salary int,
    @job nvarchar(max)
)
RETURNS TABLE
AS
BEGIN
    RETURN INSERT INTO information -- not work
    VALUES(@id,@name,@surname,@salary,@job)
END

2 Answers

1
Gordon Linoff On Best Solutions

Functions are much more limited in their functionality than are stored procedures.

Although insert is allowed, it is only allowed in local variables. As the documentation says:

  • INSERT, UPDATE, and DELETE statements modifying local table variables.

On the other hand, a stored procedure can return a value. Normally, this is a status code, where 0 means everything succeeded, and any other value means that the process failed.

1
Albert On

If you want to return the newly created table, you can use the stored procedure to do that. If you're using SQL Server, the code would be:

BEGIN
    INSERT INTO information -- not work
    VALUES(@id,@name,@surname,@salary,@job);
    SELECT * FROM information WHERE id = @@identity; -- this is the primary key just created.
END