SQL Server temporary stored procedures vs Snowflake anonymous stored procedures

Over the years, I’ve done some work with Snowflake. In fact, I got certified in it at one point, and I’ve stayed across its capabilities. I have friends that work there.

Overally, I found the SQL language that Snowflake offered to be pretty limited, and particularly at the time I spent the most time on it, it seemed to have gaps in what it provided.

One thing that fascinated me though was the idea of an anonymous stored procedure.

Similar to the way that when you use a CTE, you create one or more rowsets that do not exist outside of that same query, with a Snowflake Anonymous Stored Procedure, you can do the same thing for a stored procedure.

You can see it documented here: CALL (with anonymous procedure)

In the example above, get_top_customers is a procedure that doesn’t exist outside the calling query. Because it’s not stored, you also don’t need to have permission to create a procedure.

It’s a little weird ot call this “anonymous” when it has a name. Some SQL engines do support something closer to what I’d consider a truly anonymous procedure, with syntax like this:

CALL (procedure body is defined here without any name);

SQL Server

The closest SQL Server equivalent is a temporary stored procedure. That exists beyond the query, until it’s either dropped, or until the session where it is created ends.

I almost never see these used.

I’d love to hear your thoughts on whether you find the idea of a temporary (or indeed an anonymous) stored procedure useful.

2025-04-22