November 1, 2013 at 8:50 am
I am trying to call the following user defined function in an update statement but getting an error. Can someone point out what Im doing wrong?
The Function:
USE [OversellTool]
GO
/****** Object: UserDefinedFunction [dbo].[GetActiveEventBackOrderUnitsCount] Script Date: 11/1/2013 10:48:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GetActiveEventBackOrderUnitsCount]
(
-- Add the parameters for the function here
@prmTimeStamp datetime,
@prmOMSID bigint,
@prmEventStartDate datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @BackOrderUnits int
-- Add the T-SQL statements to compute the return value here
Select @BackOrderUnits = Sum(Convert(int,[Qty])) from DailyBackorderSkusSnapshot where [OMS ID] = @prmOMSID and [TimeStamp] >= @prmTimeStamp and [TimeStamp] < @prmEventStartDate
-- Return the result of the function
RETURN @BackOrderUnits
END
And below is how I trying to use it:
update tblEvent
set [BackOrderUnits] = QTYOrdered + (Select GetActiveEventBackOrderUnitsCount (e.[TimeStamp], e.[OMSID], e.EventStartDate))
November 1, 2013 at 8:53 am
Can you give us the error you getting. Also please provide the create table statements for tblEvent and DailyBackorderedSkusSnapshot along with some test data.
November 1, 2013 at 9:00 am
Msg 195, Level 15, State 10, Line 4
'GetActiveEventBackOrderUnitsCount' is not a recognized built-in function name.
November 1, 2013 at 9:07 am
November 1, 2013 at 9:19 am
Thanks, that did the trick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply