Creating function - 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 2005 Forums

Transact-SQL (2005)

Creating function

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 05:56:37

Hi,

I am trying to create a function..
Can anyone please look into the syntax and let me know if any issues... Also help me write a query to call this function as what i am trying is not working...

CREATE FUNCTION [dbo].[maxGPSUsage]
(@counterid INT
, @assetid INT
, @order VARCHAR(10)
, @iFromDate DATETIME = NULL
, @iUpToDate DATETIME = NULL )
RETURNS INT
AS

Begin
RETURN

(select top(1) usagemeter
from gpsCountervalues
where lastUpdated between @iFromDate and @iUpToDate
and counteruid = @counterid
and GpsCounterValues.assetuid = @assetid
order by GpsCounterValuesUid + @order
)
End

Thanks,
Sourav

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-23 : 05:59:56

CREATE FUNCTION [dbo].[maxGPSUsage]
(@counterid INT
, @assetid INT
, @order VARCHAR(10)
, @iFromDate DATETIME = NULL
, @iUpToDate DATETIME = NULL )
RETURNS table
AS
RETURN

(select top(1) usagemeter
from gpsCountervalues
where lastUpdated between @iFromDate and @iUpToDate
and counteruid = @counterid
and GpsCounterValues.assetuid = @assetid
order by GpsCounterValuesUid + @order
)

Madhivanan

Failing to plan is Planning to fail

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 06:11:39

I am getting this error when i try using this function:

select maxGPSUsage(1004, 150023626, 'ASC', '04/21/2009 00:00:00', '05/05/2009 23:59:59')

'maxGPSUsage' is not a recognized built-in function name.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-23 : 06:16:47

u need to write

select * from maxGPSUsage(1004, 150023626, 'ASC', '04/21/2009 00:00:00', '05/05/2009 23:59:59')

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 06:52:17

This works for me.. but i wanted to make it like a system defined function
so that i do not need to use select * from function.. but could use as select function.. directly..

Thanks,
Sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 06:53:28

quote:
Originally posted by ayamas

u need to write

select * from maxGPSUsage(1004, 150023626, 'ASC', '04/21/2009 00:00:00', '05/05/2009 23:59:59')

No!
The function does not return a table.
What OP needs to do, is to prefix function name with schema name.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 06:54:29

SELECT dbo.maxGPSUsage(1004, 150023626, 'ASC', '04/21/2009 00:00:00', '05/05/2009 23:59:59')

This is documented in Books Online, the excellent Microsoft SQL Server help file.

E 12°55'05.63"
N 56°04'39.26"

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 07:01:48

@Peso,

I am getting following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.maxGPSUsage", or the name is ambiguous.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:19:18

Which schema did you use when creating your function? Use that schema.
Also, I think you are trying to make a dynamic ORDER BY with the "+ @Order" thingy.
That is not possible in a function. You will have to write each case out.

E 12°55'05.63"
N 56°04'39.26"

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 07:28:28

Peso,

I used the same schema.. but it doesn't work for me.. Let me know if something looks wrong..
Yeah, even i was wondering for the order by clause.. i've modified it using Case now..

Thanks,
Sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:31:00

Add a schema for the FROM clause too

from schema.gpsCountervalues

E 12°55'05.63"
N 56°04'39.26"

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 07:42:05

PESO,

It is fine it works for me..
But can you help correct this for now.. final help

ALTER FUNCTION [dbo].[maxGPSUsage]
(@counterid INT
, @assetid INT
, @order INT
, @iFromDate DATETIME = NULL
, @iUpToDate DATETIME = NULL )
RETURNS INT
AS
BEGIN
RETURN

(If @order = 0
BEGIN
select top(1) usagemeter
from gpsCountervalues
where lastUpdated between @iFromDate and @iUpToDate
and counteruid = @counterid
and GpsCounterValues.assetuid = @assetid
order by GpsCounterValuesUid asc
END
ELSE
BEGIN
select top(1) usagemeter
from gpsCountervalues
where lastUpdated between @iFromDate and @iUpToDate
and counteruid = @counterid
and GpsCounterValues.assetuid = @assetid
order by GpsCounterValuesUid desc

END
)
END

i just want to use condition for asc or desc..

Thanks,
Sourav

saran_d28
Starting Member

36 Posts

Posted - 2009-06-23 : 08:20:41

CREATE FUNCTION MAXUSAGE
(your parameters)
RETURNS @T1 TABLE(
USEMETER INT)
AS
BEGIN
IF @ORDER = 0
BEGIN
INSERT INTO @T1
your select query
END
ELSE
BEGIN
INSERT INTO @T1
your select query
END
END

Thanks
Saravanan

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 08:45:12

Saran,

This ain't working ...
Getting error:

The last statement included within a function must be a return statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:48:48

[code]ALTER FUNCTION dbo.maxGPSUsage
(
@CounterID INT,
@AssetID INT,
@Order INT,
@iFromDate DATETIME = NULL,
@iUpToDate DATETIME = NULL
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT TOP(1) UsageMeter
FROM dbo.GpsCounterValues
WHERE LastUpdated >= @iFromDate
AND LastUpdated < DATEADD(DAY, 1, @iUpToDate)
AND CounterUID = @CounterID
AND AssetUID = @AssetID
ORDER BY (1 - 2 * SIGN(ABS(@Order))) * GpsCounterValuesUID
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources