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.
Okay, but… Why?
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 🙂