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

Question

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

Which dbms? (This doesn't look like ANSI SQL...) – jarlh May 05 '15 at 14:09 — jarlh, May 05 '15 at 14:09

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

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.

edited May 23 '17 at 10:24

Community

1
1

answered May 05 '15 at 14:12

melodiouscode

2,105
1
20
41

Unfortunately I'm dealing with multiple DBS on multiple servers. – Eriadora Otsito May 05 '15 at 14:16
I'm not sure on how the collate function works either.. do I collate the first DB to the second DB's collation or vise versa?) – Eriadora Otsito May 05 '15 at 14:17
That depends on what you want to do. Normally you would collate from the larger codepath to the smaller one. – melodiouscode May 05 '15 at 14:51
1

You could also use Linked server instead. There are easy to setup and allow cross SQL Server Query. Using Linked server Stephan's solution will come handy to keep it short. – AXMIM May 05 '15 at 15:09

gvee · Accepted Answer · 2015-05-05T14:28:44.400

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
;

score 1 · Answer 3 · answered May 05 '15 at 14:54

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'
)

This is very nice also.. Lookup table. didn't think of that. Thank you! — Eriadora Otsito, May 05 '15 at 15:23

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

3 Answers3