Function return table variable

Question

I'm trying to create a function that return a table variable.So firstly i get data from Table1 and put it in another table variable. Here i want check if this variable isempty the function return the parameter result else return the result of the table variable The function script is bellow :

USE[DATABase1]
GO

IF OBJECT_ID (N'CodeFunc', N'TF') IS NOT NULL DROP FUNCTION dbo.CodeFunc;
GO

CREATE FUNCTION CodeFunc ( @Code nvarchar(4) , @Table nvarchar(40) = '' )
RETURNS @VirtualDAT TABLE
       (
        RowID      INT    IDENTITY ( 1 , 1 ),
        Code   nvarchar(400)
       )
    AS 
    BEGIN
        DECLARE     @CodeM  nvarchar(400)
        DECLARE     @imax       INT SET         @imax = @@ROWCOUNT 
        DECLARE     @i          INT SET         @i =  1

        DECLARE  @SelectDAT TABLE
           (
            RowID      INT    IDENTITY ( 1 , 1 ),
            Code   nvarchar(400)
           )
        INSERT @SelectDAT
            SELECT Code FROM   table1
                WHERE  table1.id = 41

        IF(EXISTS (SELECT 1 FROM @SelectDAT))
            BEGIN
                WHILE (@i <= @imax)
                BEGIN 
                     SELECT  @CodeM = Code FROM @SelectDAT WHERE  RowID = @i
                     INSERT INTO @VirtualDAT(Code) VALUES (@CodeM)

                SET @i = @i + 1
                END
            END

        ELSE 
            INSERT  INTO @VirtualDAT(Code) VALUES (@Code)

RETURN
END

So this script works without put it inside function.

And i test this function like this :SELECT * FROM dbo.CodeFunc( 'toto',Default ) the result is : IF(EXISTS (SELECT 1 FROM @SelectDAT)) no record returned esle the result is ok

score 3 · Accepted Answer · answered Jan 26 '16 at 12:45

As VR46 says. The @@ROWCOUNT will be set to 0 because there is no query before it. Any code executing in a function happens as a seperate set of queries. It was probably returning a value outside the function because you had previously used the query window for another unrelated query

You could re-factor this function quite dramatically. Look below, @@ROWCOUNT will work here as it is just after the insert query and will definitely have a value based on the insert.

I have not been able to test this, but I think something like this should do the same job.

USE[DATABase1]
GO

IF OBJECT_ID (N'CodeFunc', N'TF') IS NOT NULL DROP FUNCTION dbo.CodeFunc;
GO

CREATE FUNCTION CodeFunc ( @Code nvarchar(4) , @Table nvarchar(40) = '' )
RETURNS @VirtualDAT TABLE
       (
        RowID      INT    IDENTITY ( 1 , 1 ),
        Code   nvarchar(400)
       )
    AS 
    BEGIN

        insert into @VirtualDAT
        Select Code from table1 where table1.id = 41

        if @@ROWCOUNT = 0
        begin
            INSERT  INTO @VirtualDAT(Code) VALUES (@Code)
        end
RETURN
END

Pரதீப் · Answer 2 · 2016-01-26T12:29:43.907

Since you are assigning @imax with @@ROWCOUNT right after declaration of variable will be initialized with zero.

From MSDN @@ROWCOUNT

Returns the number of rows affected by the last statement.

If am not wrong you need to assign value to @imax after the insert into..select query.

INSERT @SelectDAT
            SELECT Code FROM   table1
                WHERE  table1.id = 41
SET @imax= @@ROWCOUNT

You can do the same in SET BASED APPROACH without using while loop.

CREATE FUNCTION Codefunc (@Code  NVARCHAR(4), 
                          @Table NVARCHAR(40) = '') 
returns @VirtualDAT TABLE ( 
  rowid INT IDENTITY ( 1, 1 ), 
  code  NVARCHAR(400)) 
AS 
  BEGIN 
      IF EXISTS (SELECT code 
                 FROM   table1 
                 WHERE  table1.id = 41) 
        BEGIN 
            INSERT INTO @VirtualDAT 
                        (code) 
            SELECT code 
            FROM   table1 
            WHERE  table1.id = 41 
        END 
      ELSE 
        INSERT INTO @VirtualDAT 
                    (code) 
        VALUES      (@Code) 

      RETURN 
  END

Function return table variable

2 Answers2