Usage of Array in Sql Possible?

I am trying to write a stored proicedure that gets all the townID's from Region_TownPage table. Then i should get the City, Stateinitials of all the townID's.

Alter PROCEDURE [dbo].[GetTownDetailsforRegionID]
@RegionID int

AS
BEGIN
Declare @townID int

    set @townID = (Select townID from Region_TownPage where regionID =@RegionID) 

SET NOCOUNT ON;

    Se开发者_运维百科lect City, StateInitials,TownID from TownPage where TownID =@townID 


END

I do not know how to use an array here in sql. If someone could help me doing this, i really appreciate that.

Thanks in advance!!

I don't think you need an array - you just need to join the tables?

Select r.RegionId,
       t.TownId,
       t.City, 
       t.StateInitials

From Region_TownPage r
Join TownPage t on r.TownId = t.TownId
Where r.RegionId = @RegionId

you would declare a table variable instead of an int. so it would be something like

DECLARE @tab table(townID int)

INSERT INTO @tab
SELECT townID from Region_TownPage WHERE regionID = @RegionID

Select * From TownPage WHERE TownID IN(SELECT townID FROM @tab)