Error while creating a table-valued function inside SQL Server

Question

I'm really a beginner in SQL Server programming. I'm writing a table-valued user defined function but when i execute it I get this errors:

*Msg 178, Level 15, State 1, Procedure Select_info_FN, Line 10
A RETURN statement with a return value cannot be used in this context.
Msg 102, Level 15, State 31, Procedure Select_info_FN, Line 12
Incorrect syntax near 'BEGIN'.*

Here's my code :

create function Select_info_FN() returns table
as
begin
    declare @count int
    SELECT @count = COUNT(*) FROM dbo.info  
    if @count = 0
    begin
        INSERT INTO dbo.info VALUES (NULL, NULL, NULL, NULL, NULL)  
    end
    return (SELECT * FROM dbo.info)
end

Well you can't perform an INSERT in a function anyway, so even if you correct the other part of the syntax I don't think you're going to get too far... — Aaron Bertrand
– Aaron Bertrand, Commented Jan 30, 2013 at 0:50
You should consider moving this logic to a stored procedure. See this post: stackoverflow.com/a/5088380/1073631 -- Good luck. — sgeddes
– sgeddes, Commented Jan 30, 2013 at 0:53
You can perform an INSERT in a table-valued function it it's an insert into the result table, but it's true that you can't perform an insert into a table that exists in the database. — gmm
– gmm, Commented Jan 30, 2013 at 0:53

Community · Accepted Answer · 2017-05-23 12:04:47Z

Try something like the following. I don't know what the column names and types are, but you can replace Column* with your column schema.

CREATE FUNCTION Select_info_FN()
RETURNS @result TABLE
(
    Column1 INT,
    Column2 INT,
    Column3 INT,
    Column4 INT,
    Column5 INT
)
AS
BEGIN
    IF (SELECT COUNT(*) FROM dbo.info) = 0
    BEGIN
        INSERT INTO @result VALUES (NULL, NULL, NULL, NULL, NULL)  
    END
    ELSE
    BEGIN
        INSERT INTO @result
                SELECT Column1, Column2, Column3, Column4, Column5 FROM dbo.info
    END
    RETURN
END
GO

However, if your goal is to modify an existing table rather than build a table of data for use in queries, then Aaron Bertrand is correct that you can't do an insert in a function, and sgeddes is correct that you should use a stored procedure. In that case, try something like the following.

CREATE PROCEDURE Select_info
AS
BEGIN
    IF (SELECT COUNT(*) FROM dbo.info) = 0
    BEGIN
        INSERT INTO dbo.info (Column1, Column2, Column3, Column4, Column5)
                VALUES (NULL, NULL, NULL, NULL, NULL)  
    END
    SELECT * FROM dbo.info
END
GO

Collectives™ on Stack Overflow

Error while creating a table-valued function inside SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related