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

Answer 1

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

Answer 2

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.