Sql-server – Converting a string to an object reference

datatypessql servert-sqltype conversion

Sometimes I end up in a position where I have a string version of my object reference like so:

'server.database.schema.table'

And I need to use it as an actual object reference:

'server.database.schema.table'+'.'+[column]

How do I turn that string into:

[server].[database].[schema].[table]

so I can actually use it?

I'm sure this has been asked before, and it's probably newbish, but I just can't find the right keywords. I'm not using it in an EXEC EXECUTE.

Per the comments, here's an example dataset:

CREATE TABLE #example
(
    Server NVARCHAR(255) NOT NULL
    ,[Database] NVARCHAR(255) NOT NULL
    ,[Schema] NVARCHAR(255) NOT NULL
    ,[Table] NVARCHAR(255) NOT NULL
    ,Field1 INT
    ,Field2 DATE
    ,Field3 VARCHAR(50)
)

INSERT INTO #example
(Server,[Database],[Schema],[Table],Field1,Field2,Field3)
VALUES ('LinkedServer','DB','dbo','TableA',15,CONVERT(DATE,'1-1-18'),'Text')

So, if I wanted to select the reference of the first row, I'd imagine I'd do something like:

DECLARE @SERVER ??, @DB ??, @SCHEMA ??, @TABLE ??

SELECT TOP 1 @SERVER = Server FROM #example
SELECT TOP 1 @DB = [Database] FROM #example
SELECT TOP 1 @SCHEMA = [Schema] FROM #example
SELECT TOP 1 @TABLE = [Table] FROM #example

SELECT * FROM
    @SERVER.@DB.@SCHEMA.@TABLE
WHERE 
    Field1 = (SELECT TOP 1 Field1 FROM #example)
    AND Field2 = (SELECT TOP 1 Field2 FROM #example)
    AND Field3 = (SELECT TOP 1 Field3 FROM #example)

Best Answer

What you're looking to do is called dynamic SQL.

Using your example, I think you want to do something like this:

DECLARE @SERVER sysname, 
        @DB sysname, @SCHEMA sysname, 
        @TABLE sysname,
        @FIELD1 INT,
        @FIELD2 DATE,
        @FIELD3 VARCHAR(50);

SELECT TOP 1 @SERVER = Server,
             @DB = [Database], 
             @SCHEMA = [Schema],
             @TABLE = [Table],
             @FIELD1 = Field1,
             @FIELD2 = Field2,
             @FIELD3 = Field3
FROM #example
--ORDER BY Server
--You need to order by a unique column or set of columns to make this determinstic

DECLARE @StringToExecute NVARCHAR(MAX) = N''

SET @StringToExecute += N'
SELECT * 
FROM ' 
    + QUOTENAME(@SERVER)
    + N'.'
    + QUOTENAME(@DB)
    + N'.'
    + QUOTENAME(@SCHEMA)
    + N'.'
    + QUOTENAME(@TABLE) 
    + N'
WHERE 
    Field1 = @sp_FIELD1
    AND Field2 = @sp_FIELD2
    AND Field3 = @sp_FIELD3
';

PRINT @StringToExecute;

EXEC sys.sp_executesql @StringToExecute, 
                       N'@sp_FIELD1 INT, @sp_FIELD2 DATE, @sp_FIELD3 VARCHAR(50)',
                       @sp_FIELD1 = @FIELD1, @sp_FIELD2 = @FIELD2, @sp_FIELD3 = @FIELD3;

Take note of a couple things:

  • I'm using QUOTENAME to escape objects that I can't parameterize -- the server, database, schema, and table names.

  • I'm using sp_executesql to parameterize what I can, to prevent SQL injection.

I'll link to it again, because it's a really important thing to read: The Curse and Blessings of Dynamic SQL

Related Question