One of the things you cannot do in an UDF is to "change the state of the outside world", which
includes modifying data. I.e., you cannot do an insert (except for a table variable and possible a
temp table) inside an UDF.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog:
http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Adarsh" <shahadarsh@gmail.com> wrote in message
news:1141193051.117367.104660@j33g2000cwa.googlegroups.com...
> Hi,
>
> This code gives me error:
>
> CREATE FUNCTION [dbo].[InsertUserNotification]
> (
> @NotificationID int
> ,@UserID varchar(200)
> ,@TableName varchar(30)
> ,@ColumnName varchar(30)
> ,@RecordID bigint
> ,@RecordNo varchar(100)
> ,@RecordDescription varchar(250)
> ,@CreatedBy int
> )
> RETURNS bit
> AS
> BEGIN
> DECLARE @ReturnValue bit
> BEGIN TRY
> INSERT INTO tbl_NotifyUser
> (NotificationID
> ,UserID
> ,TableName
> ,ColumnName
> ,RecordID
> ,NotifiedFlag
> ,DeletedFlag
> ,RecordNo
> ,RecordDescription
> ,CreatedBy)
> VALUES
> (@NotificationID
> ,@UserID
> ,@TableName
> ,@ColumnName
> ,@RecordID
> ,0
> ,0
> ,@RecordNo
> ,@RecordDescription
> ,@CreatedBy)
>
> SET @ReturnValue = 1
> END TRY
> BEGIN CATCH
>
> --SET @ErrorCode=ERROR_NUMBER();
>
> --SELECT @ErrorMessage = ERROR_MESSAGE(),
> -- @ErrorSeverity = ERROR_SEVERITY(),
> -- @ErrorState = ERROR_STATE();
>
> --RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState)
>
> SET @ReturnValue = 0
> END CATCH
> RETURN @ReturnValue
> END
>
>
> The error is :
>
> Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY'
> within a function.
> Invalid use of side-effecting or time-dependent operator in 'INSERT'
> within a function.
> Invalid use of side-effecting or time-dependent operator in 'END TRY'
> within a function.
> Invalid use of side-effecting or time-dependent operator in 'BEGIN
> CATCH' within a function.
> Invalid use of side-effecting or time-dependent operator in 'END CATCH'
> within a function.
>
