How to create new table within function in sql

Question

I created one function, That function return the table values like below

CREATE FUNCTION dbo.splitText(@strArgs VARCHAR(4000))
    RETURNS @tab TABLE
    (
        [Key] VARCHAR(255) NOT NULL,
        Value VARCHAR(4000) NOT NULL
    )
    AS
    BEGIN

INSERT INTO @tab VALUES('Key1', 'Value1')
INSERT INTO @tab VALUES('Key2', 'Value2')

RETURN
END
GO

OUtput:
Key Value
*************
Key1    Value1
Key2    Value2

The second function i need,is to return the table values from the above fuction.

CREATE FUNCTION dbo.TableValuedParameterExample11()
RETURNS  @TmpTable1  table (Value VARCHAR(4000) NOT NULL) 
AS
BEGIN
 DECLARE @StateDescp VARCHAR(250)
 Select * into TmpTable1  from (Select value from dbo.Splittext('Test'))  aa

 RETURN  
END
GO

after finishing the functions,i am running the below query.

Select * from TmpTable1.

Output i need

Value 
********
Value1
Value2

I need this out put.

But I got error

Invalid use of a side-effecting operator 'SELECT INTO' within a function.

You need to use a stored procedure. DDL is not allowed in functions — user330315
– user330315, Commented Sep 30, 2016 at 6:18
its work but i need to call the function many times. So need to over come i can use "Select * from TmpTable1" this query @Nadeem — Meline
– Meline, Commented Sep 30, 2016 at 6:36
If the TmpTable1 in the second function is a base table you cannot insert any record into it because functions can't you need to use a stored procedure, it will work with the declared table. — Nadeem
– Nadeem, Commented Sep 30, 2016 at 6:38

Tedo G. · Accepted Answer · 2016-09-30 06:32:31Z

7

When you write select * into [table]... you must be sure the [table] doesnot exist. use insert into [table] select ... instead. also, you need a @ when you deal with variable or function table:

CREATE FUNCTION dbo.TableValuedParameterExample11()
RETURNS  @TmpTable1  table (Value VARCHAR(4000) NOT NULL) 
AS
BEGIN
 DECLARE @StateDescp VARCHAR(250)
 INSERT INTO
    @TmpTable1([Value])
 SELECT 
    value 
 FROM 
    dbo.SplitArgs('Test'))  aa

 RETURN  
END
GO

answered Sep 30, 2016 at 6:32

Tedo G.

1,5653 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Meline Over a year ago

After run this function. Can i use the Temptable. Like this "Select * from @TmpTable1". Tedo.G

gofr1 Over a year ago

No you cannot, but you can do a JOIN to this function like: SELECT * FROM SomeTable s INNER JOIN dbo.TableValuedParameterExample1 () d ON d.[value]=s.[value]

Meline Over a year ago

Pls explain with above sample @gofr1

gofr1 Over a year ago

@Meline I just have :) in my previous comment with query example. Also you can declare @temptable and insert from function into it like INSERT INTO @temptable SELECT * FROM dbo.TableValuedParameterExample1 () or use #temptable like SELECT * INTO #temptanle FROM dbo.Table aluedExample1 () and then work with temtable

Collectives™ on Stack Overflow

How to create new table within function in sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related