5

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

  • 2
    You are close, but basically if you want to pass in parameters of the linked server to query, the SQL you execute will need to be dynamic SQL. – Shaulinator May 7 at 16:04
  • 2
    What error(s) are you getting? – John Eisbrener May 7 at 16:13
  • 1
  • @BobKlimes I managed to achieve this using the selected answer, However, as an opportunity to try something different I used synonyms in another example annd achieved the same results with less code - I followed the outline in this article sqlblog.toolsoftonline.com/?p=78 – edwardinchains May 8 at 14:12
  • 1
    If the linked server name and database are fixed, a synonym will work. With the server name and database as variables, though, the implication is that those would be, well, variable. I suppose you could drop and re-create the synonyms every time, which would lead to similar code anyway. If the server name and database name are fixed, I see no need to use variables at all, since the additional code isn't justified by the savings in typing the name multiple times. YMMV. – Aaron Bertrand May 8 at 16:00
8

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'HOSTNAME\SERVER',
         @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'HOSTNAME\SERVER',
         @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.

  • Excellent - This worked a charm, as I mentioned in a comment above - I also rewrote the query using synonyms - which also achieved the same results. – edwardinchains May 8 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.