SQL: How Do you Declare multiple paramaters as one?

Question

I am attempting to do the following


 1. Link two tables via a join on the same database
 2. Take a column that exists in both FK_APPLICATIONID(with a slight difference, 
    where one = +1 of the other I.e. Column 1 =1375 and column 2 = 1376
 3. In one of the tables exist a reference number (QREF1234) and the other
       contains 11 phonenumbers
 4. I want to be able to enter the Reference number, and it returns all 11
       phonenumbers as a single declarable value.
 5. use "Select * from TableD where phonenum in (@Declared variable)

Here is what I have so far,

Use Database 1

DECLARE @Result INT;

SELECT @Result = D.PhoneNum1, 

FROM Table1 

JOIN  TABLE2 D on D.FK_ApplicationID= D.FK_ApplicationID

where TABLE1.FK_ApplicationID = D.FK_ApplicationID + 1 
and QREF = 'Q045569/2'
Use Database2

Select * from Table3 where PhoneNum = '@result'

I apologise to the people below who didn't understand what I was trying to achieve, and I hope this clears it up.

Thanks

Answer 1

There are a few options but the best answer depends on what you are really trying to achieve.

There is a SQL trick whereby you can concatenate values into a variable, for example;

create table dbo.t (i int, s varchar(10))
insert dbo.t values (1, 'one')
insert dbo.t values (2, 'two')
insert dbo.t values (3, 'three')
go

declare @s varchar(255)

select @s = isnull(@s + ', ', '') + s from t order by i
select @s

set @s = null
select @s = isnull(@s + ', ', '') + s from t order by i desc
select @s

Alternatively, if you just want one value then you can use the TOP keyword, for example;

select top 1 @s = s from t order by i 
select @s

select top 1 @s = s from t order by i desc
select @s

Alternatively, you can use three-part-naming and just join across the databases, something like;

SELECT T.*
FROM   DB1.dbo.Table1
       JOIN DB1.dbo.Table2 D
         ON D.FK_ApplicationID = D.FK_ApplicationID
       JOIN DB2.dbo.Table T
         ON T.PhoneNum = RIGHT(D.PhoneNum1, 11)
WHERE  DB1.dbo.FK_ApplicationID = D.dbo.FK_ApplicationID + 1
       AND Hidden = 'VALUE'

Hope this helps,

Rhys