Returning Variables from Linked Servers

Question

I need to write a stored procedure which will return the value of a parameter, acquired from a Linked Server.

I have tried declaring the variable both inside and outside of the dynamic sql but it fails with a must declare variable error.

declare @srvr nvarchar(100)
declare @dbn nvarchar(50) 

set @srvr = 'ServerName'
set @dbn = 'DatabaseName'

Declare @sql nvarchar(max)

set @sql = 'declare @param nvarchar(50) set @param = (Select X from [' + @srvr + '].[' + @dbn + '].[TableName])' 

exec (@sql)
print @param

This will form the framework for multiple procedures which reside in a central database, these procedures will be called when restoring other databases into the environment forming part of 'prep script' of sorts

Any ideas please?

Thanks very much

Answer 1

You need to declare the variable twice. Once for the inner context, once for the outer context. They need not use the same names inside and out:

declare @srvr nvarchar(100)
declare @dbn nvarchar(50) 

set @srvr = N'ServerName'
set @dbn = N'DatabaseName'

Declare @sql nvarchar(max)
declare @parms nvarchar(max)
set @sql = N'set @param = (Select X from [' + @srvr + '].[' + @dbn + '].[TableName])'
set @parms = N'@param nvarchar(50) output'
declare @param2 nvarchar(50)

exec sp_executesql @sql,@parms,@param = @param2 output
print @param2