I create the following function:
CREATE FUNCTION [dbo].[AccountGroupDaily] (@TreeId int, @EffectiveDate datetime, @CodeBlockID int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@EffectiveDate)+1-DATEPART(wk,CAST(DATEPART(yy,@EffectiveDate) as CHAR(4))+'0104');
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@EffectiveDate)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@EffectiveDate) AS CHAR(2)))+1;
IF ((DATEPART(mm,@EffectiveDate)=12) AND ((DATEPART(dd,@EffectiveDate)-DATEPART(dw,@EffectiveDate))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
From the schema tree if you right click on the Function object script execute, you get the following:
declare @RETURN_VALUE int
set @RETURN_VALUE = 0
declare @TreeId int
set @TreeId = 0
declare @CodeBlockID int
set @CodeBlockID = 0
declare @EffectiveDate datetime
set @EffectiveDate = '2005-01-01 12:00:00'
EXECUTE @RETURN_VALUE = [dbo].[AccountGroupDaily] @TreeId, @CodeBlockID, @EffectiveDate
print '@RETURN_VALUE: ' + str(@RETURN_VALUE)
print '@TreeId: ' + str(@TreeId)
print '@CodeBlockID: ' + str(@CodeBlockID)
print '@EffectiveDate: ' + convert(varchar(30),@EffectiveDate)
Notice that the parameters are not in the correct order