Starting SQL: Dynamic SQL And Temporary Objects In SQL Server

Posted on by Erik Darling

The Blink Of An Eye

Temporary objects are a necessity for just about every workload I’ve ever seen. One cannot trust the optimizer with overly large and complex queries.

At some point, you’ve gotta break things up, down, or sideways, in order to stabilize a result set for better cardinality estimation on one (or both) sides of the query.

But there are some tricks and oddities around how and where you can use temporary objects in dynamic SQL.

It’s important to consider scope, and object type, when dealing with both at once.

Since they’re my least favorite, let’s start with table variables.

Well, It Beats String Splitting

Under normal circumstances, you can’t pass table variables into dynamic SQL, nor can you declare a table variable outside of dynamic SQL and use it inside.

Trying to do either one of these things will result in an error!

DECLARE @crap TABLE(id INT);
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) AS records FROM @crap;'
EXEC sp_executesql @sql;
GO
DECLARE @crap TABLE(id INT);
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) AS records FROM @crap;'
EXEC sp_executesql @sql, N'@crap TABLE (id INT)', @crap;
GO

A big, stupid, milk-brained error. But you can do it with a User Defined Type:

CREATE TYPE crap AS TABLE(id INT);
GO 

DECLARE @crap AS crap;
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) AS records FROM @crap;'
EXEC sp_executesql @sql, N'@crap crap READONLY', @crap;
GO

In the same way that you can pass Table Valued Parameters into stored procedures, you can pass them into dynamic SQL, too. That’s pretty handy for various reasons.

But passing one out, no dice.

DECLARE @crap AS crap;
DECLARE @sql NVARCHAR(MAX) = N'DECLARE @crap AS crap;'
EXEC sp_executesql @sql, N'@crap crap OUTPUT', @crap = @crap OUTPUT;
GO

There are cooler tricks you can do with dynamic SQL and table variables, though.

But of course, it might be even easier to use a temp table, so here we go.

I Don’t See Nothing Wrong

Of course, with temp tables, there is no problem using them with inner dynamic SQL

CREATE TABLE #gold(id INT);
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) AS records FROM #gold;'
EXEC sp_executesql @sql;
DROP TABLE #gold;
GO

But we don’t find nearly as much joy doing things in reverse.

DECLARE @sql NVARCHAR(MAX) = N'CREATE TABLE #gold(id INT);'
EXEC sp_executesql @sql;
SELECT COUNT(*) AS records FROM #gold;
DROP TABLE #gold;
GO

That’s why, rather than create a UDT, which gives you another dependency with not a lot of upside, people will just dump the contents of a TVP into a temp table, and use that inside dynamic SQL.

It’s a touch less clunky. Plus, with everything we know about table variables, it might not be such a great idea using them.

I’ve covered a way of creating temp tables dynamically before, so I won’t rehash it here, but that’s another neat trick you can do with temp tables.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts