THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): 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;
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.

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

Comments

 

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

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement