Returning a variable from Dynamic SQL

Question

I'm converting a procedure to use a Linked Server, because the databases that it references have now been migrated to separate Clusters. I've almost finished this work, but I'm not stuck on the following part;

declare @CustomerId int

set @CustomerId = 2

exec (N'
USE [DatabaseName]

SELECT  @DiskUsageDataObjectsKB = SUM(alloUni.used_pages) * 8
FROM        sys.tables sysTab
            INNER JOIN sys.indexes ind 
            ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1
            INNER JOIN sys.partitions parti 
            ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id
            INNER JOIN sys.allocation_units alloUni 
            ON parti.partition_id = alloUni.container_id
WHERE       SCHEMA_NAME(sysTab.SCHEMA_ID) = CAST('+ @CustomerId +' AS NVARCHAR(MAX))') AT [LinkedServer]

select @DiskUsageDataObjectsKB 

As you can see, I'm passing the CustomerId variable to this using Dynamic SQL, but I'm not sure how I get the @DiskUsageDataObjectsKB value back to the procedure, so that It can be added to our reporting database.

Would anyone have any suggestions to how this can be done, please?

Many thanks! Tom

Answer 1

You should use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

I've got the example from here.

Answer 2

Okay, so it turns out I was just over-complicating this whole thing. I just needed to add the [LinkedServer].[DatabaseName] value to the script as you normally do.

I didn't know you could do this while referencing sys objects, but you can.