如何在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.

从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.

1 个解决方案

#1

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:

当您将select *写入[table]时……你必须确保[桌子]不存在。使用insert into [table] select…代替。此外，在处理变量或函数表时，需要使用@:

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