CODEWITHSUNDEEP
sqlsql-serverfunctiont-sqlsql-server-2008-r2
Karlx Swanovski
Karlx Swanovski

Reputation: 2989

Function is not working in table variable

I created a function that will return a varchar

CREATE FUNCTION dbo.Test
(
    @i INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @it VARCHAR(MAX)
    SET @it = 'INSERT INTO @Test
                VALUES (1)
    '

    RETURN @it

END

I tried to used that function in the query below but got an error.

DECLARE @d VARCHAR(MAX)

DECLARE @Test TABLE
(
    i INT
)

SET @d = dbo.Test(1)

SELECT @d

EXEC (@d)

SELECT * FROM @Test

Why function is not working in table variable?

How to make it work?

Upvotes: 0

Views: 544

Answers (4)

Paresh J
Paresh J

Reputation: 2419

Try this: If we try to use Select from @test from outside its declaration scope then it will not work. Hence, it has been SET in @it.

CREATE FUNCTION [dbo].[Test]
(
    @i INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @it VARCHAR(MAX)
    SET @it = 'DECLARE @Test TABLE(i INT); INSERT INTO @Test VALUES (1); SELECT * FROM @Test'

    RETURN @it

END

DECLARE @Test TABLE
(
    i INT
)

DECLARE @d VARCHAR(MAX)

SET @d = dbo.Test(1)

SELECT @d

INSERT INTO @Test
EXEC (@d)

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

If your goal is to have a function that builds one or more insert statements that can then be executed to populate a table, that cannot work using a Table Variable as those are not available in other scopes (i.e. sub-process or parent-process scopes). The only way to do something like this is:

  1. Use a local temporary table (i.e. #Temp)
  2. The function can return a string that has insert statements that insert into the local temp table.
  3. The local temp table needs to be created before the string with the INSERT statements is executed.
  4. Then you can execute the string. Because the temp table already exists, it can be accessed by a sub-process (i.e. the EXEC).

Example:

CREATE FUNCTION dbo.Test
(
    @i INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @it VARCHAR(MAX);
    SET @it = 'INSERT INTO #Test (Col1)
                VALUES (' + CONVERT(VARCHAR(20), @i) + ');
    '

    RETURN @it;
END;

And use in this manner:

DECLARE @SQL VARCHAR(MAX);

--DROP TABLE #Test;
CREATE TABLE #Test
(
    Col1 INT
);

SET @SQL = dbo.Test(1);
SET @SQL += dbo.Test(55);

SELECT @SQL;

EXEC (@SQL);

SELECT * FROM #Test;

Upvotes: 0

Rahul
Rahul

Reputation: 77876

Try doing it this way by using a temporary table instead of table variable since table variable won't be accessible as already pointed by others in comment. [Not Tested]

CREATE FUNCTION dbo.Test
(
    @i INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @it VARCHAR(MAX)
    SET @it = 'CREATE TABLE #Test(i INT); INSERT INTO #Test
                VALUES (1)'

    RETURN @it

END

Then call your function like

DECLARE @d VARCHAR(MAX)

SET @d = dbo.Test(1)

SELECT @d

EXEC (@d)

SELECT * FROM #Test

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

Dynamic SQL executes in a separate context, in which your variables (including table ones) are unavailable.

The nearest thing to what you are trying to do is, probably, this:

insert into @Test
exec(@d);

whereas function will return only the select part.

Don't forget, though, that in Microsoft SQL Server you cannot nest insert into ... exec statements.

Upvotes: 0

Related Questions