Usage of Array in Sql Possible?

Question

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;

    Select 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!!

codingbadger · Accepted Answer · 2010-12-27 15:07:38Z

5

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

answered Dec 27, 2010 at 15:07

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JoshBerke Over a year ago

Assuming this question is his actual query I would agree this is the best approach. Otherwise the other answer of using a table might be preferable depending on complexity of query

devman Over a year ago

agreed. this would be the way to do it unless you needed to keep the townIDs in memory for something in the SP later on but for your small example, a join would be the better solution.

devman · Accepted Answer · 2010-12-27 15:05:11Z

4

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)

answered Dec 27, 2010 at 15:05

devman

1,5492 gold badges11 silver badges24 bronze badges

Collectives™ on Stack Overflow

Usage of Array in Sql Possible?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related