SQL: Calling a Scalar UDF with EXEC

SQL: Calling a Scalar UDF with EXEC

Most SQL Server developers are aware that the EXEC statement can be used to:

  • Execute a stored procedure (system, user-defined, extended)
  • Execute some dynamic SQL

And most understand that you can SELECT from a scalar user-defined function.

But the option that many people don’t seem to be aware of, is that you can also use EXEC to call a scalar function.

I remember noticing this in the documentation for the EXEC command some years back. Prior to that, it had never dawned on me that you could use EXEC to call a scalar UDF. It’s also in the oldest documentation that I was able to check, so I’d say it’s worked for a long time.

Here’s a trivial example of a function and calling it as I would have in the past:

USE tempdb;  
GO

CREATE FUNCTION dbo.SayHello(@WhoTo NVARCHAR(100))  
RETURNS NVARCHAR(120)  
AS  
BEGIN  
  RETURN N'Hello ' + @WhoTo;  
END;  
GO

SELECT dbo.SayHello(N'Greg');  
GO

Now clearly I’d never use a function like that. The performance impacts would be horrid. But I wanted to keep this very simple.

Now, notice that you can also call it like this:

DECLARE @ReturnValue NVARCHAR(120);

EXEC @ReturnValue = dbo.SayHello N'Greg';

SELECT @ReturnValue;

Prior to the day back then when I read the documentation for EXEC, I would have guessed this wouldn’t work.

Learn more about Advanced T-SQL

If you really want to learn about SQL Server Advanced T-SQL, we have an online on-demand course that you can enrol in, right now. You’ll find it at SQL Server Advanced T-SQL for Developers and DBAs

2026-04-12