2

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)
  • 3
    Can you give an example of how you'd use the code you're trying to run? – Erik Darling Oct 19 '18 at 19:23
  • TSQL has objects? – paparazzo Oct 21 '18 at 17:12
  • @paparazzo I'm used to Hyperion Business Intelligence; idk the appropriate term. – undrline Oct 21 '18 at 19:48
  • @sp_BlitzErik I was trying to keep it general because it comes up quite often. My current project I literally have a table with columns server, database, schema, table, and then a list of various fields I need to anti-join if they're all a match. For those first columns, the values are stored as nvarchar(255). – undrline Oct 21 '18 at 19:51
  • Okay, cool, go ahead and update your question with an example of what you're trying to do with that very literal table full of very literal values. – Erik Darling Oct 21 '18 at 20:04
2

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

  • But that turns the whole thing into a string (lol, you even called it stringtoexecute), and uses EXEC sp_executesql ... there's no other way to piece them together and convert their names into the reference? you're declaring them as sysname, but that clearly isn't enough. – undrline Oct 21 '18 at 23:49
  • @undrline No, this is how you have to do things. Can you be more specific about sysname not being enough? The datatype length is the max length any one of those object names can be. – Erik Darling Oct 22 '18 at 0:24
  • I just meant I had played around with trying to convert/cast and declare things as sysname before I asked the question. I tried to upvote, but it wouldn't let me because of rep; I thought there was an exception for your own question. QUOTENAME was something I had been meaning to look up but hadn't gotten around to yet +1 for that even though it doesn't look I get a rep exemption for it being my question. – undrline Oct 22 '18 at 0:55
  • @undrline you should be able to mark the answer as accepted, if you feel it's adequate. – Erik Darling Oct 22 '18 at 1:08

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.