Otdfbt

I currently have a local server linked to a remote server.

Is it possible to create a stored procedure within the local server, but querying data from tables within the linked server. I am aware there may be performance issues, but I am reluctant to create the sp on the linked server as it would require obtaining permissions to do so.

I believe my problem is syntax but I cannot identify exactly what,

USE [LOCALDB] --my local database or should I reference linked server db here
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[mystoredprocedure]
 DECLARE @linkedserv NVARCHAR(150)
 DECLARE @linkeddb NVARCHAR(150)

 SET @linkedserv = 'HOSTNAMESERVER';
 SET @linkeddb = 'remotedb';

 SELECT (CASE LTRIM(RTRIM([COLUMN1]))
 WHEN '' Then ''
 WHEN 'THIS' THEN 'THAT'
 WHEN 'NOW' THEN 'NEVER'
 ELSE 'OTHER' END) [Options]
 INTO #TempTable
 FROM @linkedserv.@linkeddb.dbo.TableOnLinkedServer --is this an issue?
 FULL OUTER JOIN OtherTableOnLinkedServer ON TableOnLinkedServer.COUMN1 = 
 OtherTableOnLinkedServer.COLUMN0 

The linked servers db and it's tables are queried multiple times throughout the sp so finding a shorthand way of referencing them would be great,

Cheers

You can't parameterize entity names into a T-SQL statement. In order to do this you need to (a) create the #temp table first, and (b) use dynamic SQL. Here is one approach:

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @exec nvarchar(256),
 @linkedserv nvarchar(150),
 @linkeddb nvarchar(150),
 @sql nvarchar(max);

 SELECT @linkedserv = N'HOSTNAMESERVER',
 @linkeddb = N'remotedb';

 SELECT @exec = QUOTENAME(@linkedserv) + N'.'
 + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

 CREATE TABLE #TempTable([Options] varchar(64));

 SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
 WHEN '''' THEN ''''
 WHEN ''THIS'' THEN ''THAT''
 WHEN ''NOW'' THEN ''NEVER''
 ELSE ''OTHER'' END) AS [Options]
 FROM dbo.TableOnLinkedServer AS t
 FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
 ON t.COLUMN1 = ot.COLUMN0;';

 INSERT #TempTable([Options]) EXEC @exec @sql; 

 SELECT * FROM #TempTable;
END
GO

You could do it all inside dynamic SQL, so if knowing the columns up front is a challenge, there is a way. But it's messy, assuming you actually need a #temp table, because then everything you do with #TempTable has to be done inside the dynamic SQL.

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @exec nvarchar(256),
 @linkedserv nvarchar(150),
 @linkeddb nvarchar(150),
 @sql nvarchar(max);

 SELECT @linkedserv = N'HOSTNAMESERVER',
 @linkeddb = N'remotedb';

 SELECT @exec = QUOTENAME(@linkedserv) + N'.'
 + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

 SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
 WHEN '''' THEN ''''
 WHEN ''THIS'' THEN ''THAT''
 WHEN ''NOW'' THEN ''NEVER''
 ELSE ''OTHER'' END) AS [Options]
 INTO #TempTable
 FROM dbo.TableOnLinkedServer AS t
 FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
 ON t.COLUMN1 = ot.COLUMN0;

 SELECT * FROM #TempTable;';

 EXEC @exec @sql; 
END
GO

Please look at the obligatory Erland Sommarskog article on dynamic T-SQL to ensure you don't end up creating an embarrassing SQL Injection vulnerability.