User Defined Function Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

User Defined Function Error

Author

Topic

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 07:22:40

hello friends

Hope this problem solved by u as soon as possible

CREATE FUNCTION [dbo].[GetReport]
(
-- Add the parameters for the function here

)
RETURNS @Report TABLE
(
-- Add the column definitions for the TABLE variable here
[Personnel_id] [int] NOT NULL,
[Head_code] [char](20),
[Head_amount] [float] NOT NULL,
[Application_from] [datetime] NOT NULL,
[Application_upto] [datetime] NOT NULL
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Personnel_id INT;
DECLARE @Head_code CHAR(20);
DECLARE @Head_amount FLOAT;
DECLARE @wef DATETIME;

-- Local variable for current date
DECLARE @CurrentDate DATETIME;

-- A cursor declaration for all the rows of the table
DECLARE CurGenerate CURSOR FOR
SELECT
Personnel_id,
Head_code,
Head_amount,
wef
FROM dbo.Table_1

OPEN CurGenerate;

FETCH CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

-- Set CurrentDate to First Day of current Month and year
SET @CurrentDate = dateadd(day,-day(GETDATE()),dateadd(month,1,GETDATE()))
--dateadd(day,-day(getdate( ))+1,getdate( )) ;

WHILE (@@FETCH_STATUS = 0)
BEGIN

/*PRINT @Personnel_id;
PRINT @Head_code;
PRINT @Head_amount;
PRINT @wef;*/

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

WHILE(@wef < @CurrentDate)
BEGIN

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

END

FETCH NEXT FROM CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

END

CLOSE CurGenerate;
DEALLOCATE CurGenerate;

RETURN
END

but error comes in the line
SET @CurrentDate = dateadd(day,-day(GETDATE()),dateadd(month,1,GETDATE()))

Server: Msg 443, Level 16, State 1, Procedure GetReport, Line 41
Invalid use of 'getdate' within a function.

please solve this error

With Best Regards
Ruby

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-05 : 07:24:51

GETDATE() is not allowed inside UDF due to its non-deterministic nature.

Read about this in Books online in the section "Function Determinism and Side Effects".

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 07:25:38

For fastest solution, add a new parameter to the function for which you pass getdate() as value when using/calling the function.

For speedier execution, REWRITE code and replace cursors with set-based solutions.

Peter Larsson
Helsingborg, Sweden

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-05 : 07:31:23

can u help me out where to declare
as its not working i had tried

Hope u try on ure sql server 2000

With Best Regards
Ruby

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-06-05 : 11:24:25

quote:
Originally posted by ruby_sqlserver

can u help me out where to declare
as its not working i had tried

Hope u try on ure sql server 2000

With Best Regards
Ruby

it means, at the parameter declaration. Add one more parameter which will accept date n later u can use the parameter in the place of GetDate().

let us know,

Mahesh

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 03:17:34

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[GetReport2]

(
@CurrDate DATETIME
-- Add the parameters for the function here

)
RETURNS @Report TABLE
(
-- Add the column definitions for the TABLE variable here
[Personnel_id] [int] NOT NULL,
[Head_code] [char](20),
[Head_amount] [float] NOT NULL,
[Application_from] [datetime] NOT NULL,
[Application_upto] [datetime] NOT NULL
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Personnel_id INT;
DECLARE @Head_code CHAR(20);
DECLARE @Head_amount FLOAT;
DECLARE @wef DATETIME;

-- Local variable for current date
DECLARE @CurrentDate DATETIME;

-- A cursor declaration for all the rows of the table
DECLARE CurGenerate CURSOR FOR
SELECT
Personnel_id,
Head_code,
Head_amount,
wef
FROM dbo.Table_1

OPEN CurGenerate;

FETCH CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

-- Set CurrentDate to First Day of current Month and year
SET @CurrentDate = dateadd(day,-day(@CurrDate),dateadd(month,1,@CurrDate))
--dateadd(day,-day(getdate( ))+1,getdate( )) ;

WHILE (@@FETCH_STATUS = 0)
BEGIN

/*PRINT @Personnel_id;
PRINT @Head_code;
PRINT @Head_amount;
PRINT @wef;*/

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

WHILE(@wef < @CurrentDate)
BEGIN

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

END

FETCH NEXT FROM CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

END

CLOSE CurGenerate;
DEALLOCATE CurGenerate;

RETURN
END

The command(s) completed successfully.

wen i run this function

select * from dbo.GetReport2()

Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.GetReport2.

Now y this error come
i declare a parameter as said

waiting for reply

With Best Regards
Ruby

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 03:22:24

Declaration of parameter is not sufficient. When you invoke the function, you need to pass value for that parameter.

Declare @d datetime
set @d = getdate()
select * from dbo.GetReport2(@d)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-06 : 03:38:10

if i do like this

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[GetReport2]

(

-- Add the parameters for the function here

)
RETURNS @Report TABLE
(
-- Add the column definitions for the TABLE variable here
[Personnel_id] [int] NOT NULL,
[Head_code] [char](20),
[Head_amount] [float] NOT NULL,
[Application_from] [datetime] NOT NULL,
[Application_upto] [datetime] NOT NULL
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Personnel_id INT;
DECLARE @Head_code CHAR(20);
DECLARE @Head_amount FLOAT;
DECLARE @wef DATETIME;
Declare @d Datetime
set @d = GETDATE();
-- Local variable for current date
DECLARE @CurrentDate DATETIME;

-- A cursor declaration for all the rows of the table
DECLARE CurGenerate CURSOR FOR
SELECT
Personnel_id,
Head_code,
Head_amount,
wef
FROM dbo.Table_1

OPEN CurGenerate;

FETCH CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

-- Set CurrentDate to First Day of current Month and year
SET @CurrentDate = dateadd(day,-day(@d),dateadd(month,1,@d))
--dateadd(day,-day(getdate( ))+1,getdate( )) ;

WHILE (@@FETCH_STATUS = 0)
BEGIN

/*PRINT @Personnel_id;
PRINT @Head_code;
PRINT @Head_amount;
PRINT @wef;*/

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

WHILE(@wef < @CurrentDate)
BEGIN

INSERT INTO @Report(Personnel_id,Head_code,Head_amount,Application_from,Application_upto)
VALUES(@Personnel_id,@Head_code,@Head_amount,@wef, dateadd(day,-day(@wef),dateadd(month,1,@wef)));

SET @wef = DATEADD(month,1,@wef);

END

FETCH NEXT FROM CurGenerate
INTO @Personnel_id,@Head_code,@Head_amount,@wef;

END

CLOSE CurGenerate;
DEALLOCATE CurGenerate;

RETURN
END

then error coming

Server: Msg 443, Level 16, State 1, Procedure GetReport2, Line 32
Invalid use of 'getdate' within a function.

so u had to change in above post according to my function
not individual

Hope u got

With Best Regards
Ruby

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 03:44:40

You need to keep date parameter for the function:

CREATE FUNCTION [dbo].[GetReport2]
(
@CurrDate DATETIME
)
...

But while calling the function pass value of getdate() to the function like this:

Declare @d datetime
set @d = getdate()
select * from dbo.GetReport2(@d)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources