Selecting into a Variable Using Dynamic SQL

Asking how to select into a variable using dynamic SQL is a frequent question on newsgroups because SQL Server will not let you select into a variable using the EXEC() function. To get around this, you can create a temporary table and build the SQL dynamically to insert a row into it with the value you want to SELECT into a variable. You can then EXEC() the SQL and SELECT the value into the variable from the temporary table.

For example, suppose you wanted to create a stored procedure that returned the number of rows in
a table: