CODEWITHSUNDEEP
sqlsql-serversql-function
Abhijeet
Abhijeet

Reputation: 13856

Call User defined function as System Function in SQL server

I have created an user defined functions in SQL server. Is it possible to call these function in select statement, just like any other built-in function?

Create function [dbo].[TIME_TO_SEC](@inputTime time)
returns INT
As
Begin 
    DECLARE @secDiff INT = datediff(second, '00:00:00', @inputTime)
    return @secDiff;
end

Currently calling this as below:

DECLARE @ret int;
EXEC TIME_TO_SEC @inputTime='01:00:00'
select @ret;

I am attempting something as simple as:

select TIME_TO_SEC('01:00:00') from TimeTable

Is it feasible in SQL-server?

Upvotes: 0

Views: 838

Answers (3)

cyan
cyan

Reputation: 747

if you get an issue with your function, try to add "WITH EXECUTE AS CALLER"

Create function [dbo].[TIME_TO_SEC](@inputTime time)
Returns int
WITH EXECUTE AS CALLER
As
Begin 
    DECLARE @secDiff INT = datediff(second, '00:00:00', @inputTime)
    return @secDiff;
end

then call the function with select

select  dbo.[TIME_TO_SEC](getdate())

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

It's possible, but you need to reference the function by the user's schema

select dbo.TIME_TO_SEC('01:00:00') from TimeTable

Upvotes: 1

JSR
JSR

Reputation: 6386

Yes, it's certainly feasible, and it looks like you've got everything you need already. Have you tried it?

Upvotes: 0

Related Questions