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 = 'HOSTNAME\SERVER';
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
Best Answer
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:
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.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.