CODEWITHSUNDEEP
sqlsql-serversql-function
Rafael Adel
Rafael Adel

Reputation: 7759

Error while creating a table-valued function inside SQL Server

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

Upvotes: 1

Views: 3316

Answers (1)

gmm
gmm

Reputation: 478

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

Upvotes: 1

Related Questions