THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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;

  RETURN N'Hello ' + @WhoTo;

SELECT dbo.SayHello(N'Greg');

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.

Published Saturday, August 13, 2011 6:52 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Adam Machanic said:

Okay, but... Why?

August 13, 2011 9:54 AM

Greg Low said:

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

August 15, 2011 6:54 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement