Distributed query question (really a t-sql question)

Question

Post reply

Distributed query question (really a t-sql question)

cygnus2112

SSC Veteran

Points: 202
More actions
April 8, 2005 at 9:15 am

#61851

Hi Folks,
Having trouble using a local variable in the FROM section of a SELECT statement (not sure if this is even possible):
CREATE PROCEDURE sel_AllCompanies
    @ServerName           VARCHAR(50),
    @DatabaseName       VARCHAR(50),
    @Owner                   VARCHAR(50)
AS
    DECLARE @server VARCHAR(50)
    DECLARE @Target VARCHAR(100)
    DECLARE @Table      VARCHAR(50)
    SET @Table = 'Company'
    SET @server = '[' + LTRIM(RTRIM(@ServerName)) + ']'
    SET @Target = @server + '.' + @DatabaseName + '.' + @Owner + '.' + @Table
    SELECT Name
       FROM @Target
GO

This generates a 'Must declare the variable @Target'.
Can I even use a local variable in the FROM section of a SELECT statement?

Thanks as always,
Bryan

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can't do it like this (you're asking sqlserver to select from a table variable) try :

EXEC ('Select name from ' + @Target)

please read this before using dynamic sql :

The Curse and Blessings of Dynamic SQL

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>Can I even use a local variable in the FROM section of a SELECT statement?

No. Search on "Dynamic SQL" and "SQL Injection Attack" for related forum threads & articles.

cygnus2112 SSC Veteran Points: 202 More actions · Answer 3

Thanks Remi, that did the trick. And thanks for the link, I've printed and read the article.

I guess I now have a 'bigger picture' type question regarding this issue.

I'm writing a Windows service that will be installed on 'server Z'. This service will be executing SP's located on 'server Z' which in turn will be querying tables on 'server A', 'server B', 'server C', etc. The service will determine which server to query based on events external to SQL Server. The servers that are being queried are production servers and I cannot install custom sevices on these boxes, therefore 'server Z' is the only place this Windows service can be setup.

I think I'll be 'safe' in using dynamic SQL in this case, since all SP execution (and parameters passed to those SPs) will be generated by the Windows service and not by any user interaction.

However, I thought I'd ask all you experienced gurus out there for your thoughts and opinions. Maybe there's a much better way of doing this that I'm missing.

Thanks so much!

Bryan