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