Error message when using getdate() within a UDF Call.

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)

Error message when using getdate() within a UDF Call.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-30 : 09:46:39

Iain writes "Hi, please help....I have a UDF for which I need to pass in the current date and time, so I have declared a variable in the parameters list as follows:

CREATE FUNCTION fnSelectWIPS (@TodayDate SMALLDATETIME)
RETURNS @WIPList TABLE
(
WIP NVARCHAR(8),
AreaDesc NVARCHAR(40),
CustomerName NVARCHAR(35),
TimeDueOut DATETIME,
KeyTag INT,
RegNo NVARCHAR(8),
Arrived BIT,
Paused BIT,
WarningYN BIT,
Alert BIT

)
AS
BEGIN
INSERT INTO @WIPList (WIP, AreaDesc, CustomerName, TimeDueOut, KeyTag, RegNo, Arrived, Paused, WarningYN, Alert)
SELECT dbo.tblWIPs.WIP, dbo.tblArea.AreaDesc, dbo.tblKerridgeWIPData.CustomerName, dbo.tblWIPs.TimeDueOut, dbo.tblWIPs.KeyTag,
dbo.tblKerridgeWIPData.RegNo, dbo.tblWIPs.CarArrived, 0, 0, 0
FROM dbo.tblWIPs INNER JOIN
dbo.tblKerridgeWIPData ON dbo.tblWIPs.WIP = dbo.tblKerridgeWIPData.WIP INNER JOIN
dbo.tblArea ON dbo.tblWIPs.CurrentArea = dbo.tblArea.Area
ORDER BY dbo.tblWIPs.WIP

/*This is where the cursor begins*/

DECLARE @WIPNo NVARCHAR(8)
DECLARE @Arrived BIT
DECLARE @Paused BIT
DECLARE @Warning DATETIME
DECLARE @Alert BIT

/*Paused Cursor*/
DECLARE NotArrivedCursor CURSOR
FOR
SELECT WIP FROM @WIPList

OPEN NotArrivedCursor

FETCH NEXT FROM NotArrivedCursor INTO @WIPNo

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Arrived= (SELECT dbo.tblWIPs.CarArrived
FROM dbo.tblWIPs
WHERE dbo.tblWIPs.WIP = @WipNo)

IF @Arrived = 1
BEGIN
UPDATE @WIPList
SET Paused = 1
WHERE WIP = @WipNo
END

FETCH NEXT FROM NotArrivedCursor INTO @WIPNo
END

/*Warning Cursor*/
DECLARE @WarnMins INT
DECLARE @WarnType CHAR(6)

SELECT @WarnMins = (SELECT WarnMins FROM tblParameters)
SELECT @WarnType = (SELECT WarnType FROM tblParameters)

DECLARE WarningCursor CURSOR
FOR
SELECT WIP FROM @WIPList

OPEN WarningCursor

FETCH NEXT FROM WarningCursor INTO @WIPNo

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Warning= (SELECT dbo.tblWIPs.TimeDueOut
FROM dbo.tblWIPs
WHERE dbo.tblWIPs.WIP = @WipNo)

IF @WarnType = 'Before'
BEGIN
IF (DATEDIFF(MINUTE, @Warning, @TodayDate)) <= @WarnMins
BEGIN
UPDATE @WIPList
SET WarningYN = 1
WHERE WIP = @WipNo
END
END

IF @WarnType = 'After'
BEGIN
IF (DATEDIFF(MINUTE, @TodayDate, @Warning)) <= @WarnMins
BEGIN
UPDATE @WIPList
SET WarningYN = 1
WHERE WIP = @WipNo
END
END

FETCH NEXT FROM WarningCursor INTO @WIPNo
END

CLOSE NotArrivedCursor
CLOSE WarningCursor
DEALLOCATE NotArrivedCursor
DEALLOCATE WarningCursor

RETURN

END

Now, when I come to call this function as follows:

select * From dbo.fnSelectWIPs (getdate())

I get the following error message:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.

Please help, I am in desparate need...

Regards

Iain"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-30 : 10:01:33

You can't pass getdate() directly to a UDF in that fashion.

try:
declare @test smalldatetime
set @test = getdate()
select * from dbo.fnSelectWIPs( @test )

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 15:21:53

Man, I hate cursors....


CREATE FUNCTION fnSelectWIPS (@TodayDate SMALLDATETIME) 
RETURNS @WIPList TABLE 
( 
WIP NVARCHAR(8), 
AreaDesc NVARCHAR(40), 
CustomerName NVARCHAR(35), 
TimeDueOut DATETIME, 
KeyTag INT, 
RegNo NVARCHAR(8), 
Arrived BIT, 
Paused BIT, 
WarningYN BIT, 
Alert BIT 

) 
AS 
BEGIN 

insert @WIPList (
    WIP,
    AreaDesc,
    CustomerName,
    TimeDueOut,
    KeyTag,
    RegNo,
    Arrived,
    Paused,
    WarningYN,
    Alert ) 
SELECT
    dbo.tblWIPs.WIP,
    dbo.tblArea.AreaDesc,
    dbo.tblKerridgeWIPData.CustomerName,
    dbo.tblWIPs.TimeDueOut,
    dbo.tblWIPs.KeyTag,
    dbo.tblKerridgeWIPData.RegNo,
    dbo.tblWIPs.CarArrived,
    case
        when dbo.tblWIPs.cararrived = 1 then 1
        else 0
    end as Paused,  -- and thus the NotArrivedCursor is slain
    case
        when ((select warntype from tblParameters) = 'Before' and
              datediff(mm, tblWIPs.timedueout, @todaydate <= (select
                                                                  warnmins
                                                              from
                                                                  tblparameters)) then 1,
        when ((select warntype from tblParameters) = 'After' and
              datediff(mm, tblWIPs.timedueout, @todaydate <= (select
                                                                  warnmins
                                                              from
                                                                  tblparameters)) then 1,
        else 0
    end as WarningYN,  -- and may the WarningCursor rest in pieces
    0
FROM
    dbo.tblWIPs
    INNER JOIN dbo.tblKerridgeWIPData
        ON dbo.tblWIPs.WIP = dbo.tblKerridgeWIPData.WIP
    INNER JOIN dbo.tblArea
        ON dbo.tblWIPs.CurrentArea = dbo.tblArea.Area 
ORDER BY dbo.tblWIPs.WIP 
END
go

<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-31 : 15:24:25

Wow ... put that in your pipe and smoke it!

setBasedIsTheTruepath
<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 15:29:07

Something about friday afternoons and cursors . . . I'm like Stallone in Over the Top . . . I put my hat on backwards and it's like flipping the switch . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources