What is wrong with this UDF? MS SQL 2K5

[Mark Gambo]

Here is my User Defined Function that I am using with MS SQL 2K5:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_InsertSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_InsertSplit]
GO
 
CREATE  FUNCTION fn_InsertSplit(
    @TableName nVarChar(100),
    @FieldName nVarChar(100),
    @text varchar(8000),
    @delimiter varchar(20) = ' '
)
 
    RETURNS nvarchar(10)
 
AS
 
BEGIN
    DECLARE @index int
    DECLARE @SQL as nVarChar(1000)
    DECLARE @Result nVarChar(100)
 
    SET @index = -1 
 
    WHILE (LEN(@text) > 0) 
      BEGIN  
        SET @index = CHARINDEX(@delimiter , @text)  
        IF (@index = 0) AND (LEN(@text) > 0)  
            BEGIN   
                SET @SQL = 'INSERT ' + @TableName + '(' + @FieldName + ') VALUES(' + (LEFT(@text, @index - 1)) + ')'
                EXEC sp_executesql @SQL
                BREAK  
              END  
        IF (@index > 1)  
            BEGIN   
                SET @SQL = 'INSERT ' + @TableName + '(' + @FieldName + ') VALUES(' + (LEFT(@text, @index - 1)) + ')'
                EXEC sp_executesql @SQL
                SET @text = RIGHT(@text, (LEN(@text) - @index))  
            END  
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) - @index)) 
        END
    SET @RESULT = 'Done'    
    RETURN @RESULT
END

and I am calling it like this:

SELECT IADATA.dbo.fn_InsertSplit('dbo.College_CLASSES','College_CLASS','A,B,C,D,E,F',',')

I am getting the following error:

Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.

[brucevde]

Within a UDF, you cannot execute Insert, Update, Delete statements that will affect database tables.
sp_ExecuteSQL is not an "extended stored procedure".

[Mark Gambo]

Within a UDF, you cannot execute Insert, Update, Delete statements that will affect database tables.
sp_ExecuteSQL is not an "extended stored procedure".

Hmmm, so I guess I am out of luck then?

[szlamany]

Within a UDF, you cannot execute Insert, Update, Delete statements that will affect database tables.

Really?

I've never tried to do anything but SELECT's in UDF's.

But it's amazing to me that they prohibit insert, update and delete statements.

Triggers can do insert, update and delete - I always considered a UDF just a cousin of a trigger.

[kaffenils]

What you want to do is create a Multistatement Table-valued Function.

The function returns a table representing the splitted values, and you reference it as any other table in an INSERT statement.

[szlamany]

Table Value Functions are my new best friend...

I put together a code bank example of using it - with the CROSS APPLY JOIN that really makes then useful

http://www.vbforums.com/showthread.php?t=502948

[Mark Gambo]

What you want to do is create a Multistatement Table-valued Function.

The function returns a table representing the splitted values, and you reference it as any other table in an INSERT statement.

But I don't know the name of the table that I want to insert the data into, that is why I am using the Dynamic SQL string.

[techgnome]

Why a function? Should be a stored procedure straight up....

-tg

[Mark Gambo]

Why a function? Should be a stored procedure straight up....

-tg

Yeah, I was thinking about that but I never created a function before and I wanted to see what they could do. Basically, what is the difference between a SPROC and a Function? They both can return values, correct?

Thanks!

[szlamany]

Back in SQL 2000 - only SPROCS and UDF's

SPROC could be EXECUTED.

You could take the results of the EXEC and put them into a TEMP TABLE in another SPROC.

But you could not JOIN to the SPROC nor include it in the SELECT list.

UDF's - you could include in the SELECT list.

Keep in mind that the traditional reason for a UDF is for a scalar function - such as formatting a phone #'s with ()-'s or an SSN - pass in a string - pass back out a string. Or a number... You get my meaning?

At any rate - now - with TABLE VALUE FUNCTIONS - the lines are blurred. TVF's can return a table. Can be JOIN'd to in a SELECT. The power of that is tremendous. Now a TVF is basically a VIEW with CODE!

[Mark Gambo]

Hmm, I need a little more time to wrap my brain around this. Thanks Steve!