Passing Server Name into T-SQL Stored Procedure

Question

I have a stored procedure that is being called by an app from different environments (DEV/QA). From the stored procedure below, I wanted to pass the server name as a parameter, replacing "HARDCODEDSERVER" with the one passed as parameter "@Server"

CREATE PROCEDURE sp_MySTOREDPROCEDURE
   @Id Int,
   @Server nvarchar(20)
AS
BEGIN

 DECLARE @TempTable Table (
 -- Declarations
 )

 DECLARE @TempTable2 Table (
 -- Declarations 
 )

 INSERT INTO @TempTable
  SELECT a.* FROM Table a
   Inner Join [HARDCODEDSERVER].[DatabaseName].dbo.[TABLE1]
   -- And so ON

 INSERT INTO @TempTable2
  SELECT b.* FROM Table b
   Inner Join [HARDCODEDSERVER].[DatabaseName].dbo.[TABLE1]
   -- And so ON

 BEGIN TRANSACTION
 BEGIN
    UPDATE @TempTable  -- rest of the process for TempTable
    UPDATE @TempTable2  -- rest of the process for TempTable2
    -- The rest of the SP
 END
 COMMIT TRANSACTION

END

I have used the dynamic SQL approach, but this doesn't work since it cannot scope @TempTable even previously defined already:

DECLARE @SQL nvarchar(2000);

SET @SQL = '
 INSERT INTO @TempTable
  SELECT a.* FROM Table a
   Inner Join [ ' + @Server + '].[DatabaseName].dbo.[TABLE1]
   -- And so on
'

EXECUTE sp_executesql @SQL

Tried the same for EXEC but same issue where it can't find @TempTable:

EXEC ( '
 INSERT INTO @TempTable
  SELECT a.* FROM Table a
   Inner Join [ '' + @Server + ''].[DatabaseName].dbo.[TABLE1]
   -- And so on
')

I have checked other answers here in SO but most only works on single operations. Any help where the server name can be parameterized and still be able to work/scope on the Temp Tables?

If you convert the table variables to temp tables then you will be able to access them in the scope of a dynamic sql call. Or, if you want to keep them as table variables, then you'll have to pass them as parameters in a call to sp_executesql. In SQL Server Lower scopes have access to temp tables created in the scope of a higher level caller. — squillman, Mar 30 '22 at 20:11
Asides: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. Don't use the `sp_` prefix for stored procedures as explained [here](https://sqlnuggets.com/do-not-prefix-stored-procedures-with-sp_/). — HABO, Mar 30 '22 at 20:15
Also things like servername should be typed as *sysname*; Using temp tables would be the preferable and easier option. — Stu, Mar 30 '22 at 20:17
@squillman - I have used instead temp tables and you are correct. The EXEC() function now sees the tables and I was able to pass the correct server name in the queries! — Patrick, Mar 30 '22 at 20:34
Or you could just put the whole thing including the table variables inside dynamic SQL — Charlieface, Mar 30 '22 at 22:15

score 2 · Answer 1 · answered Mar 30 '22 at 22:24

One option is to use temp tables (created using CREATE TABLE #tablename) as these are visible to any procedures or dynamic SQL called from the same connection.

Another option is to create this procedure on the remote server itself. Then you can call it like this:

CREATE PROCEDURE sp_MySTOREDPROCEDURE
   @Id Int,
   @Server sysname
AS

DECLARE @procName nvarchar(1035) = @Server + '.[Databasename].[dbo].[sp_MySTOREDPROCEDURE]';

EXEC @procName @Id = @Id;

go

Alternatively, just place the whole procedure into dynamic SQL, rather than just the INSERT statement

CREATE PROCEDURE sp_MySTOREDPROCEDURE
   @Id Int,
   @Server sysname
AS

DECLARE @sql nvarchar(max) = '

 DECLARE @TempTable Table (
 -- Declarations
 );

 DECLARE @TempTable2 Table (
 -- Declarations 
 );

 INSERT INTO @TempTable
  SELECT a.* FROM Table a
   Inner Join ' + QUOTENAME(@Server) + '.[DatabaseName].dbo.[TABLE1];
   -- And so ON

 INSERT INTO @TempTable2
  SELECT b.* FROM Table b
   Inner Join ' + QUOTENAME(@Server) + '.[DatabaseName].dbo.[TABLE1];
   -- And so ON

 BEGIN TRANSACTION;
    UPDATE @TempTable  -- rest of the process for TempTable
    UPDATE @TempTable2  -- rest of the process for TempTable2
    -- The rest of the SP

 COMMIT TRANSACTION;
';

EXEC sp_executesql
  @sql,
  N'@Id int',   --  declare parameters on this line
  @Id = @Id;    --  pass all parameters like this

go

Note the following:

Use of QUOTENAME to quote the server name
Store the server name in a sysname variable, which is the same as @nvarchar(128)
Store dynamic SQL in nvarchar(max)
Pass through parameters properly

Passing Server Name into T-SQL Stored Procedure

1 Answers1