Invalid use of side-effecting or time-dependent operator in ''INSERT'' within a function.

Question

Post reply

Invalid use of side-effecting or time-dependent operator in ''INSERT'' within a function.

me_balaji

SSC Eights!

Points: 923
More actions
March 8, 2006 at 3:01 pm

#167482

Table Structure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [SRC_AZN].[SRC_DATA_STORE](
[SRC_DATA_STORE_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[SRC_SYBASE_DB_NM] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SRC_SYBASE_DB_SRVR_NM] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SRC_DATA_STORE_NM] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SRC_DATA_STORE_TP_CD] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FACILITY_ID] [numeric](18, 0) NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[SRC_DATA_STORE_ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [SRC_DATA_STORE_AK1] UNIQUE NONCLUSTERED
(
[SRC_SYBASE_DB_NM] ASC,
[SRC_SYBASE_DB_SRVR_NM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------------------------

CREATE FUNCTION [SRC_AZN].[fn_Get_DataStoreID]
(@SRC_SYBASE_DB_NM sysname,
@SRC_SYBASE_DB_SRVR_NM sysname,
@SRC_DATA_STORE_TP_CD Varchar(10)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @SRC_DATA_STORE_ID int
IF EXISTS (SELECT 1 FROM SRC_AZN.SRC_DATA_STORE
WHERE SRC_SYBASE_DB_NM = @SRC_SYBASE_DB_NM
AND SRC_SYBASE_DB_SRVR_NM = @SRC_SYBASE_DB_SRVR_NM
AND SRC_DATA_STORE_TP_CD = @SRC_DATA_STORE_TP_CD )
BEGIN
SELECT @SRC_DATA_STORE_ID = SRC_DATA_STORE_ID
FROM SRC_AZN.SRC_DATA_STORE
WHERE SRC_SYBASE_DB_NM = @SRC_SYBASE_DB_NM
AND SRC_SYBASE_DB_SRVR_NM = @SRC_SYBASE_DB_SRVR_NM
AND SRC_DATA_STORE_TP_CD = @SRC_DATA_STORE_TP_CD
END
ELSE
BEGIN
INSERT INTO SRC_AZN.SRC_DATA_STORE(SRC_SYBASE_DB_NM,SRC_SYBASE_DB_SRVR_NM,SRC_DATA_STORE_NM,SRC_DATA_STORE_TP_CD,FACILITY_ID)
VALUES (@SRC_SYBASE_DB_NM,@SRC_SYBASE_DB_SRVR_NM,@SRC_SYBASE_DB_NM+ '-' + @SRC_SYBASE_DB_SRVR_NM, @SRC_DATA_STORE_TP_CD,1)
SELECT @SRC_DATA_STORE_ID = SCOPE_IDENTITY()
END
RETURN @SRC_DATA_STORE_ID
END

When I try to create the function I am getting following error. No triggers or constraints created on this table except Identity column. Can somebody tell me why I am getting this error.
" Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function"
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

You're not allowed to do DML (like the insert) inside scalar functions.

/Kenneth

Sheckster SSC-Addicted Points: 401 More actions · Answer 2

hi, I'm experiencing the same problem.

Is there a way around this?, I need to 'insert' then do a 'select' inside a function.

thanks

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Why do you 'need' that? This is not what functions is ment to do.

Use a procedure instead, if possible, or rethink your strategy if you find yoursefl in a corner.

Both procedures and functions are basically just different ways of packaging T-SQL code. Both have their pros and cons and both have their limits and regulations on what you can and can't do.

Use the one method that suits your particular purpose. There is no one-size-fits-all.

/Kenneth