I have a very basic test stored procedure, shown below. This proc is reading data in a different database, in a different server. To do this I am using a linked server. From what I have read, I need to change the FROM clause to this:
[linked server name].[database name].[schema name].[table name]
However, I would like to pass in the linked server name and database name as parameters and use them in my FROM clause. I am not concerned with injection attacks, etc. I will be passing this in from a config file.
create PROC [dbo].[SelectTEST]
@GU UNIQUEIDENTIFIER,
@LINKED_SERVER_NAME nvarchar(max),
@DATABASE_NAME nvarchar(max)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
SELECT [GU]
FROM '[' + @LINKED_SERVER_NAME +'].['+ @DATABASE_NAME + '].[Test Table] '
WHERE ([GU] = @GU OR @GU IS NULL)
COMMIT
This is a big mess of syntax errors. Is it possible to pass in these parameters and use in my stored procedure? I would have to make this change to a bunch of different procs, so sorta trying to find the a succinct solution...
