Meline Meline - 1 month ago 10
SQL Question

How to create new table within function in sql

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.

Answer

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
Comments