CODEWITHSUNDEEP
sqlsql-server
Eriadora Otsito
Eriadora Otsito

Reputation: 13

SELECT case using a variable which can be set based on a parameter

I'd like to select a particular value from a table while using an information from another database that is set based on a current database's value.

So a select case to find the operator code and set the DB path.. then use the same path and collate the result. 

DECLARE @DB varchar (1000)

CASE 
WHEN @Operator= 1 THEN SET @DB = '{SERVERNAME\ENTITY\DBNAME}'
WHEN @Operator= 2 THEN SET @DB = '{SERVERNAME2\ENTITY2\DBNAME2}'
WHEN @Operator= 3 THEN SET @DB = '{SERVERNAME3\ENTITY3\DBNAME3}'

Select transItem_item collate SQL_Latin1General_CI_AS
FROM Group_Transactions
INNER JOIN @DB.Table_Trans
ON (transItem.item_id collate SQL_Latin1General_CI-AS = Table_Trans.item_id)
Where ---Condition

Upvotes: 0

Views: 53

Answers (3)

Stephan
Stephan

Reputation: 6018

Gvee's Control Flow method may be a verbose, but it would work. You might want to create a look up table like my @tbl_Databases if you have a bunch of databases. Here's a dynamic SQL solution:

DECLARE @Operator INT = 1,
        @DB VARCHAR(1000);

DECLARE @tbl_Databases TABLE (ID INT IDENTITY(1,1),DB VARCHAR(1000))
INSERT INTO @tbl_Databases(DB)
VALUES ('{SERVERNAME\ENTITY\DBNAME}'),('{SERVERNAME2\ENTITY2\DBNAME2}'),('{SERVERNAME3\ENTITY3\DBNAME3}');

SELECT @DB = DB
FROM @tbl_Databases
WHERE ID = @Operator

SELECT @DB

SELECT
(
'SELECT transItem_item COLLATE SQL_Latin1General_CI_AS
FROM Group_Transactions
INNER JOIN ' + @DB + '.dbo.Table_Trans
ON (transItem.item_id collate SQL_Latin1General_CI-AS = Table_Trans.item_id)
Where 1 = 1'
)

Upvotes: 1

gvee
gvee

Reputation: 17161

Control flow method (likely to be the most efficient):

IF @Operator = 1
  BEGIN
    SELECT stuff
    FROM   Group_Transactions
     INNER
      JOIN "Server1\Instance1".Database1.Schema.Table_Trans
        ON Group_Transactions... = Table_Trans...
    WHERE  things...
    ;
  END
ELSE IF @Operator = 2
  BEGIN
    SELECT stuff
    FROM   Group_Transactions
     INNER
      JOIN "Server2\Instance2".Database2.Schema.Table_Trans
        ON Group_Transactions... = Table_Trans...
    WHERE  things...
    ;
  END
ELSE IF @Operator = 3
  BEGIN
    SELECT stuff
    FROM   Group_Transactions
     INNER
      JOIN "Server3\Instance3".Database3.Schema.Table_Trans
        ON Group_Transactions... = Table_Trans...
    WHERE  things...
    ;
  END
;

Single [conditional] query method:

SELECT Group_Transactions.stuff
     , trans1.other_thing As other_thing1
     , trans2.other_thing As other_thing2
     , trans3.other_thing As other_thing3
     , Coalesce(trans1.other_thing, trans2.other_thing, trans3.other_thing) As other_thing
FROM   Group_Transactions
 LEFT
  JOIN "Server1\Instance1".Database1.Schema.Table_Trans As trans1
    ON trans1... = Group_Transactions...
   AND trans1.things...
   AND @Operator = 1
 LEFT
  JOIN "Server2\Instance2".Database2.Schema.Table_Trans As trans2
    ON trans2... = Group_Transactions...
   AND trans2.things...
   AND @Operator = 2
 LEFT
  JOIN "Server3\Instance3".Database3.Schema.Table_Trans As trans3
    ON trans3... = Group_Transactions...
   AND trans3.things...
   AND @Operator = 3
;

Upvotes: 2

melodiouscode
melodiouscode

Reputation: 2069

If this is TSQL (I am guessing from your colation names) then you are best trying out OPENQUERY to run your join against another database server. If you are querying a database on the same server you could build your query up as a parameter and then run it using EXEC.

Upvotes: 2

Related Questions