I use this method when I need a dynamic solution:

use AdventureWorks2000

go

if exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P')

drop procedure dbo.proc_dynamic

go

create procedure dbo.proc_dynamic

@name varchar(50)

as

begin

select LocationId, [Name], rowguid from dbo.Location where [Name] = @name

end

go

DECLARE @SELECT varchar(1000),

@NAMEvarchar(50)

SET @NAME = 'Location'

SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'

EXEC(@SELECT)

go