Get the result of dynamic SQL into a variable?

I needed this kind of functionality for something at work.

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

Some of the principles work for me - haven't gotten it to do what I need, but its a great help! Can work my way around my problem now.

Read the full article here.