Multi-statement table-valued function is not working

Question

Post reply

Multi-statement table-valued function is not working

michael.leach2015

SSCommitted

Points: 1597

February 7, 2020 at 8:15 pm

#3724050

When I run the following code I get an error saying, "Incorrect syntax near the keyword 'SELECT'."

What is wrong with my code here?

CREATE FUNCTION multiStatement(@ID int)

RETURNS @multiVariable table
(
    ID      int,
    FName   varchar(20),
    Company varchar(20),
    State   char(2)
)

AS

BEGIN
    
    INSERT INTO @multiVariable
    VALUES
    SELECT ID, fName, address, state FROM baseTableExample;
    
    UPDATE @multiVariable
    SET Company = 'Texas Instruments'
    WHERE ID = 3;
    
RETURN
END
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

shughes_las SSC Enthusiast Points: 165 More actions · Answer 1

You don't need VALUES (that's if you're not doing a select for the insert, but instead a list of static values).

INSERT INTO @multiVariable

SELECT ID, fName, address, state FROM baseTableExample;

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 2

shughes_las wrote:

You don't need VALUES (that's if you're not doing a select for the insert, but instead a list of static values).
INSERT INTO @multiVariable
SELECT ID, fName, address, state FROM baseTableExample;

Just to make sure I understand what you are saying, I am trying to take the results of the SELECT statement (sub SELECT), then inserting those into @multiVariable. So because I am inserting new records with the SELECT statement, I would need to use VALUES, right?

This reply was modified 4 years, 9 months ago by michael.leach2015.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

you don't need the select

example

INSERT INTO Customer (FirstName, LastName, City, Country, Phone)

VALUES ('Craig', 'Smith', 'New York', 'USA', 1-01-993 2800)

MVDBA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Either SELECT or VALUES

DECLARE @ID int

DECLARE @multiVariable table

(

ID int,

FName varchar(20),

Company varchar(20),

State char(2)

)

INSERT INTO @multiVariable

--VALUES

SELECT ID, fName, address, state

FROM baseTableExample;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shughes_las SSC Enthusiast Points: 165 More actions · Answer 5

You use one or the other.

Values is for a static set of data that you're inserting, when it's being typed into the query itself rather than pulled from another table.

INSERT INTO table (column1, column2)

VALUES ('a','b')

Select is when you want the inserted data to come from another table.

INSERT INTO table (column1, column2)

SELECT columnA, columnB FROM table2

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 6

ChrisM@Work wrote:

Either SELECT or VALUES
DECLARE @ID int
DECLARE @multiVariable table
(
ID int,
FName varchar(20),
Company varchar(20),
State char(2)
)
INSERT INTO @multiVariable
--VALUES
SELECT ID, fName, address, state
FROM baseTableExample;

To everybody. It worked. Thank you.