Can not invoke a user defined scalar function

Question

question

Utpal asked Nov 13, '09

Can not invoke a user defined scalar function

                    
set ANSI_NULLS ON                    
set QUOTED_IDENTIFIER ON                    
GO                    
ALTER   FUNCTION [dbo].[GetCommaSeparatedValues](@TableName varchar(50), @ColumnName varchar(50))                    
RETURNS nvarchar(4000)                    
                    
AS                      
BEGIN                     
                    
    DECLARE @strValues nvarchar(4000), @strValue nvarchar(4000), @SQL varchar(500)                    
                    
    Set @SQL = 'select @strValues = ISNULL(@strValues + '', '', '''') + ' + @ColumnName + ' FROM ' + @TableName                    
                    
                    
     exec sp_executesql @SQL                    
                    
    RETURN @strValues                    
END

Hi,

I am unable to invoke the above udf. Please help

sql-server-2005 user-defined-function

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-13T11:57:54Z

Kristen answered Nov 13, '09

Note that your function is attempting to concatenate the Column from every row in the Table - this is going to be a long string if it is a large table!

Normally for a function that returns a delimited list you will include some parameters in the function definition to restrict the numebr of rows processed.

Say you have a Customer Number and you want a list of their Invoices

            
IF EXISTS (SELECT * FROM sysobjects             
  WHERE id = object_id(N'[dbo].[fnCustomerInvoiceList]')             
  AND xtype IN (N'FN', N'IF', N'TF'))            
    DROP FUNCTION dbo.fnCustomerInvoiceList            
GO            
            
CREATE FUNCTION dbo.fnCustomerInvoiceList            
(            
    @CustomerNo	varchar(10),            
    @strDelimiter	varchar(100) = ','	-- Separator for each value            
)            
RETURNS varchar(8000)            
/* WITH ENCRYPTION */            
AS            
/*            
 * fnCustomerInvoiceList    List of Invoice Numbers for a given Customer            
 *            
 * Returns:            
 *            
 *  Concatenation of Order IDs for matching Customer rows            
 *            
 * HISTORY:            
 *            
 * 13-Nov-2009 KBM  Started            
 */            
BEGIN            
DECLARE @String varchar(8000)            
            
    SELECT	@String = COALESCE(@String + @strDelimiter, '')             
    	+ CONVERT(varchar(20), O.OrderID)            
    FROM	dbo.tblCustomer AS C            
    	JOIN dbo.tblOrder AS O            
    		 ON O.OrderCustomerID = C.CustomerID            
    WHERE	    C.CustomerNo = @CustomerNo            
    ORDER BY C.CustomerNo, O.OderID            
            
    RETURN @String            
END            
/* TEST RIG            
            
SELECT  dbo.fnCustomerInvoiceList('FRED01', ', ')            
SELECT  dbo.fnCustomerInvoiceList('BILL02', '
')            
            
 */            
--================== fnCustomerInvoiceList ==================--            
GO            
            
PRINT 'Create procedure fnCustomerInvoiceList DONE'            
GO

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-13T10:29:17Z

Madhivanan answered Nov 13, '09

You can't use dynamic SQL inside a udf. Use Stored procedure

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question