Juliafrazer
asked on
im trying to create a user defined function, but cant execute it as an error message appears
when trying to run an sql 2005 user defined function, it wont execute as the following error message occurs:
Msg 443, Level 16, State 15, Procedure InsertMailingPreferences, Line 60
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
How do i resolve this?
THanks
Julia
Msg 443, Level 16, State 15, Procedure InsertMailingPreferences, Line 60
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
How do i resolve this?
THanks
Julia
USE [MembershipDB]
GO
/****** Object: UserDefinedFunction [dbo].[getUpgradeProductIdInPackage] Script Date: 10/27/2008 14:09:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[InsertMailingPreferences] (
@PackageID int,
@AddressID int,
@UserName nvarchar(100)
)
returns nvarchar(50)--int
as
BEGIN
DECLARE @Counter int
SET @Counter = 0
DECLARE @TotalRows int
DECLARE @ServiceID int
DECLARE @MailingMethodID int
DECLARE @MaxServiceID int
DECLARE @NowDate datetime
--------------------------------------------------------------
DECLARE @DefaultMailingsTable table
(
ServiceID int,
ServiceName nvarchar(50),
DefaultMailingMethodID int,
MethodDesc nvarchar(50)
)
--SET @DefaultMailingsTable = dbo.getDefaultMailingPreferencesForPackage(@PackageID)
--SET @DefaultMailingsTable = (SELECT * FROM dbo.getDefaultMailingPreferencesForPackage(@PackageID))
Insert Into @DefaultMailingsTable
SELECT * FROM dbo.getDefaultMailingPreferencesForPackage(@PackageID)
SET @TotalRows = (SELECT Count(*) FROM @DefaultMailingsTable)
SET @MaxServiceID = (SELECT Max (ServiceID) FROM dbo.getDefaultMailingPreferencesForPackage(@PackageID))
WHILE (@Counter < @MaxServiceID+1)
BEGIN
--Select ServiceID, DefaultMailingMethodID FROM @DefaultMailingsTable WHERE exists
-- (Select ServiceID, DefaultMailingMethodID FROM @DefaultMailingsTable WHERE ServiceID = @Counter)
If (Select ServiceID FROM @DefaultMailingsTable WHERE serviceID in (@Counter)) is NOT null
--(Select ServiceID, DefaultMailingMethodID FROM @DefaultMailingsTable WHERE ServiceID = @Counter)) = @Counter -- is not null
BEGIN
Set @ServiceID = (Select ServiceID FROM @DefaultMailingsTable WHERE ServiceID = @Counter)
Set @MailingMethodID = (Select DefaultMailingMethodID FROM @DefaultMailingsTable WHERE ServiceID = @Counter)
Set @NowDate = getDate()
--convert(datetime,getDate(),103)
INSERT INTO dbo.MailingUserPreferences(UserName, DateSelected, MailingMethodID, AddressID, ServiceID)
VALUES(@UserName, convert(datetime,24/05/2008,103), 1, @AddressID, @ServiceID);
SET @Counter = @Counter + 1
END
Else
Begin
SET @Counter = @Counter + 1
end
END
Return 'Success'
--Return @PackageID
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
absolutely....you can call another proc from one proc
create proc allprocs
as
exec proc1
exec proc2
exec proc3
create proc allprocs
as
exec proc1
exec proc2
exec proc3
ASKER
but this function is the second call, after a stored procedure, which then calls another function as you can see.
The problem is, when i convert the above function to a strored procecure, i cant call it from another stored procedure...or can i?
Thanks
Julia