-
Jan 24th, 2008, 10:00 PM
#1
Thread Starter
Giants World Champs!!!!
What is wrong with this UDF? MS SQL 2K5
Here is my User Defined Function that I am using with MS SQL 2K5:
T-SQL Code:
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:
T-SQL Code:
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.
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jan 25th, 2008, 12:28 AM
#2
Re: What is wrong with this UDF? MS SQL 2K5
Within a UDF, you cannot execute Insert, Update, Delete statements that will affect database tables.
sp_ExecuteSQL is not an "extended stored procedure".
-
Jan 25th, 2008, 02:20 AM
#3
Thread Starter
Giants World Champs!!!!
Re: What is wrong with this UDF? MS SQL 2K5
Originally Posted by brucevde
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?
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jan 25th, 2008, 06:43 AM
#4
Re: What is wrong with this UDF? MS SQL 2K5
Originally Posted by brucevde
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.
-
Jan 25th, 2008, 07:40 AM
#5
Re: What is wrong with this UDF? MS SQL 2K5
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.
-
Jan 25th, 2008, 08:23 AM
#6
Re: What is wrong with this UDF? MS SQL 2K5
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
-
Jan 25th, 2008, 11:02 AM
#7
Thread Starter
Giants World Champs!!!!
Re: What is wrong with this UDF? MS SQL 2K5
Originally Posted by 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.
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.
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jan 25th, 2008, 11:22 AM
#8
Re: What is wrong with this UDF? MS SQL 2K5
Why a function? Should be a stored procedure straight up....
-tg
-
Jan 25th, 2008, 12:27 PM
#9
Thread Starter
Giants World Champs!!!!
Re: What is wrong with this UDF? MS SQL 2K5
Originally Posted by techgnome
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!
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jan 25th, 2008, 12:40 PM
#10
Re: What is wrong with this UDF? MS SQL 2K5
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!
-
Jan 25th, 2008, 12:57 PM
#11
Thread Starter
Giants World Champs!!!!
Re: What is wrong with this UDF? MS SQL 2K5
Hmm, I need a little more time to wrap my brain around this. Thanks Steve!
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|