Things I learned today: Calling a scalar UDF with EXEC

One thing I love about T-SQL is that every day I seem to find another way to use it that wasn't immediately obvious to me. I was reading the SQL Server Denali documentation and noticed that in the definition of the EXEC command that it said you could execute a stored procedure or scalar function using EXEC.

While I'd obviously used it to call a stored procedure, it had never dawned on me that you could use EXEC to call a scalar UDF, so I had to try it. Sure enough, it's obviously worked for a long time. Here's an 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

Notice that you can also call it like this:

DECLARE @ReturnValue NVARCHAR(120);

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

 

SELECT @ReturnValue;

I was speaking with a member of the SQL Product Group last week and noting just how good the documentation has become in recent years. I continue to be impressed with it.

2 thoughts on “Things I learned today: Calling a scalar UDF with EXEC”

  1. Hi Adam,
    Only one I can think of off the top of my head is that it makes it easier to call a function while specifying the execution context from the client, without having to explicitly use EXEC AS and then REVERT before calling ie: you could do:
    EXEC @ReturnValue = dbo.SayHello N'Greg' WITH EXECUTE AS Fred;
    I don't doubt that something else will occur to me down the track 🙂

Leave a Reply

Your email address will not be published.