How do I get the result of dynamic SQL into a variable?

Often, people want to use dynamic SQL. While usually this indicates a lack of forethought in the data model, there is an occasional valid use. In either case, it is not very intuitive to get the result of a dynamic SQL statement into a variable. Consider the following: 
 

DECLARE @i INT    SELECT @i = COUNT(*)      FROM INFORMATION_SCHEMA.TABLES    PRINT @i

 
No problem, right? Well, what if you wanted to take the database name as a variable, and return or output the count? 
 

CREATE PROCEDURE dbo.countTables      @dbname SYSNAME  AS  BEGIN      DECLARE @i INT        SELECT @i = COUNT(*)          FROM @dbname.INFORMATION_SCHEMA.TABLES        PRINT @i  END  GO

 
This won't compile, of course; since variables are not permissible as direct representation of objects, you will get this error: 
 

Server: Msg 170, Level 15, State 1, Procedure countTables, Line 8  Line 8: Incorrect syntax near '.'.

 
Some people have tried various hacks at the syntax, like: 
 

CREATE PROCEDURE dbo.countTables      @dbname SYSNAME  AS  BEGIN      DECLARE @i INT        SELECT @i = EXEC('SELECT COUNT(*)          FROM @dbname.INFORMATION_SCHEMA.TABLES')        PRINT @i  END  GO

 
Which results in: 
 

Server: Msg 156, Level 15, State 1, Procedure countTables, Line 7  Incorrect syntax near the keyword 'EXEC'.

 
If you really want this functionality, what you'll have to do is use dynamic SQL and sp_executeSQL. Try the following example: 
 

CREATE PROCEDURE dbo.countTables      @dbname SYSNAME  AS  BEGIN      DECLARE @i INT, @sql NVARCHAR(512)        SET @sql = N'SELECT @i = COUNT(*) FROM '          + @dbname + '.INFORMATION_SCHEMA.TABLES'        EXEC sp_executesql          @query = @sql,          @params = N'@i INT OUTPUT',          @i = @i OUTPUT        PRINT @i  END  GO

 
You can see a more thorough example in KB #262499. 
 
And please, please read Erland Sommarskog's article: The curse and blessings of dynamic SQL.