More things I learned today: EXEC AS USER on dynamic SQL

I really liked the addition of the WITH EXECUTE AS clause when defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:

CREATE PROC SomeSchema.SomeProc

WITH EXECUTE AS USER = ‘Fred’

AS

… 

I’d also used the EXEC AS clause to temporarily change my execution context during testing. For example:

EXEC AS USER = ‘Fred’;

— Try some code here while running as Fred

REVERT;

But what I didn’t realize is that you can set the execution context for a single execution like this:

EXEC (‘Some command to be executed as Fred’) AS USER = ‘Fred’;

Back in SQL 2000, I made a big effort to read all of Books Online. The more I read of the current BOL, the more I think I need to do that again. (Mind you, I’m not sure that that’s even possible any more. I think they’re writing/modifying it faster than I have time to read it).

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.

Setting default values for slicers in PowerPivot

I’ve been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That’s as good as a default value for me. Thanks José!

Hope this helps someone else.