declare variable in a sql function

Question

I have a sql function and i need to declare few variables in that function. Please advise how can i achieve this.

For example i need to put -->

Declare @ClientResult TABLE(
        RowIndex int identity(1,1),
        SplitText varchar(50) 
    )

in the below function.

create FUNCTION [dbo].CLIENT_SHIPPINGREPORTDATA_Function_Test  
(                    
 @CLIENTPK_NEW TABLE,
 @CGNEEPK TABLE
 @type varchar(100)              
)                 
RETURNS TABLE                    
AS              

RETURN                 

SELECT   distinct              
OP_PartNum,            
OP_PK       
FROM Client_whsPallet pallet

I am using sql server 2005

Thanks

score 6 · Accepted Answer · edited Mar 20 '22 at 09:03

What you are after is a multi-statement table function

e.g.

CREATE FUNCTION dbo.fxnExample (@Param INTEGER)
RETURNS @Results TABLE(FieldA VARCHAR(50))
AS
BEGIN
INSERT @Results
SELECT SomeField
FROM Somewhere 
WHERE ParamField = @Param

RETURN
END

This is different to your current function which is called an "inline table valued function" and you should be aware of the differences as this could cause performance issues if you switch to the multi-statement approach. My advice would be to try and use inline table valued functions wherever possible. I recommend you checking out these articles which go into detail:

Multi-statement Table Valued Function vs Inline Table Valued Function
Link
http://sqlbits.com/Agenda/event6/High_performance_functions/default.aspx

What if values of my returned table are in fact a pivot table based on dynamic columns? Thats in fact why I'm using a Table Function... :/ — Simon Dugré, Feb 25 '15 at 19:57

score 6 · Answer 2 · answered Nov 18 '10 at 13:21

In SQL Server you can't declare variables inside of an inline table-Valued function. You'll need to create a multi-statement table valued function if you really need to declare variables in it. You would do something like this:

CREATE FUNCTION [dbo].CLIENT_SHIPPINGREPORTDATA_Function_Test
(
    @CLIENTPK_NEW TABLE, @CGNEEPK TABLE @type varchar(100)
)
RETURNS @output TABLE (OP_PartNum int, OP_PK int)
AS BEGIN

Declare @ClientResult TABLE( RowIndex int identity(1,1), SplitText varchar(50) ) 

/* more code here */

RETURN
END

Not knowing what exactly it is you are trying to do, I would see if there is away around using a multi-statement function though as you will see performance decrease.

score 5 · Answer 3 · answered Nov 18 '10 at 13:34

Compare these equivalent code samples. They show the syntax differences between inline and multistatement table-valued functions.

CREATE FUNCTION [dbo].Inline (@type varchar(100))
RETURNS TABLE
AS
RETURN
    SELECT distinct name
    FROM sysobjects
    WHERE type = @type
GO

CREATE FUNCTION [dbo].Multistatement (@type varchar(100))
RETURNS @results TABLE (name sysname)
AS
BEGIN
    INSERT @results (name)
    SELECT distinct name
    FROM sysobjects
    WHERE type = @type

    RETURN
END

score 0 · Answer 4 · answered Nov 18 '10 at 13:24

As suggested by AdaTheDev you can create a multi-statement function for returning a table from a function.
Otherwise if you need to create a table inside the function you can create a new temporary table prefixing its name with an #

create table #TableNAme (FieldA Varchar(5))

declare variable in a sql function

4 Answers4