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.

2011-08-13