Call User defined function as System Function in SQL server

Question

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?

Yes you can call a scalar function as a part of SELECT query. And you can pass column as a parameter if needed — Comfortably Numb
– Comfortably Numb, Commented May 8, 2014 at 16:17
What actually happened when you tried selecting that function as you described? — Dan Bracuk
– Dan Bracuk, Commented May 8, 2014 at 16:20

bastos.sergio · Accepted Answer · 2014-05-08 16:24:13Z

1

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

answered May 8, 2014 at 16:24

bastos.sergio

6,7644 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cyan · Accepted Answer · 2014-05-08 16:34:57Z

1

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())

answered May 8, 2014 at 16:34

cyan

7475 silver badges8 bronze badges

Comments

JSR · Accepted Answer · 2014-05-08 16:22:06Z

0

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

answered May 8, 2014 at 16:22

JSR

6,4162 gold badges22 silver badges24 bronze badges

Collectives™ on Stack Overflow

Call User defined function as System Function in SQL server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related