CODEWITHSUNDEEP
sqlsql-serverfunction
Thibaut
Thibaut

Reputation: 343

SQL query works well but not the function made from it

I am trying to make a function that returns flight duration.

It works well with a simple query but when I try with the function it return 0...

This returns 1:00

declare @FlightId int,
        @Departure datetime,
        @Arrival datetime,
        @duration varchar

set @FlightId = (select FlightId from Flight where FlightNumber = 'ZZZ')
set @Departure = (select min(Departure) from Step where FlightId = @FlightId )
set @Arrival = (select max(Arrival) from Step where FlightId = @FlightId )

select CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, @Departure, @Arrival), 0), 114)

and this is the function that returns 0

alter function FlightDuration
    (@FlightNumber varchar(50)) 
returns varchar
as 
begin
    declare @FlightId int,
            @Departure datetime,
            @Arrival datetime,
            @duration varchar

    set @FlightId = (select FlightId from Flight where FlightNumber = @FlightNumber)
    set @Departure = (select min(Departure) from Step where FlightId = @FlightId )
    set @Arrival = (select max(Arrival) from Step where FlightId = @FlightId )

    set @duration = CONVERT(varchar(12), DATEADD(minute, DATEDIFF(minute, @Departure, @Arrival), 0), 114)

    return @duration
end

I call it like this:

SELECT dbo.FlightDuration ('ZZZ')

Upvotes: 1

Views: 116

Answers (2)

edixon
edixon

Reputation: 1000

I guess that the flight 'ZZZ' does not exists. In the first case, @FlightId might have been set by a previous query. To check if the query is successful do something like this:

SET @FlightId = NULL;
SELECT @FlightId = FlightId FROM Flight WHERE ...
IF @FlightId IS NULL
  SET @duration = 'N/A'
ELSE BEGIN
  SELECT @Departure = min(Departure), @Arrival = max(Arrival) FROM Step WHERE FlightId = @FlightId;
  SET @duration = CONVERT...
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The problem could easily be the length attribute on varchar. Without a length, SQL Server defaults to a length of "1".

So, try declaring the function as:

alter function FlightDuration (
    @FlightNumber varchar(50)
) returns varchar(12)
begin
    . . .
end;

Upvotes: 2

Related Questions