Foreach statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreach statement

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-07-31 : 14:43:27

Hi,

What is a "Foreach" and how do I use it?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 14:45:27

It isn't available in SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-31 : 14:46:41

it foreaches the collection

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-07-31 : 16:01:37

Do you know what is its equivelant?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:04:07

You can use the WHILE command or a cursor in SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-07-31 : 16:48:01

Are you able to give me an idea on how to create a cursor for this?

FOREACH
SELECT
'RES',
recid1,
year_built,
main_fn_area,
uppr_fn_area,
unfin_area,
bsmt_area,
bldg_style,
quality,
condition,
num_stories,
phy_pct_good
INTO
aBldg,
aRecid,
aYrBlt,
wrkA1,
wrkA2,
wrkA3,
wrkA4,
tmpCode,
aQuality,
aCondition,
aNumSt,
aPctGood
FROM
residence,
parcel
WHERE parcel.property_id = propid
and parcel.parcel_year = taxyear
-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000
AND parcel.link_id = residence.link_id

IF wrkA1 IS NULL THEN
LET wrkA1 = 0;
END IF;
IF wrkA2 IS NULL THEN
LET wrkA2 = 0;
END IF;
IF wrkA3 IS NULL THEN
LET wrkA3 = 0;
END IF;
IF wrkA4 IS NULL THEN
LET wrkA4 = 0;
END IF;

LET aSqFt = wrkA1 + wrkA2 + wrkA3 + wrkA4;
IF aSqFt = 0 THEN
LET aSqFt = NULL;
END IF;

SELECT
description
INTO
aCode
FROM
value_list
WHERE table_name = 'RESIDENCE'
AND column_name = 'BLDG_STYLE'
AND code = TRIM(tmpCode);

IF aCode is NULL THEN
LET aCode = tmpCode;
END IF;

RETURN
aBldg,
aRecid,
aYrBlt,
aSqFt,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
with RESUME;

END FOREACH;

I am lost.

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-31 : 16:48:45

No as you haven't provided enough information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-07-31 : 16:51:54

The whole query looks like this. I was hopng to get a basic idea on how to translate the first "foreach" so I can apply it to the others.

CREATE PROCEDURE "informix".getbldg_x(propid INTEGER,
taxyear INTEGER)

RETURNING
CHAR(03),
INTEGER,
CHAR(04),
INTEGER,
CHAR(18),
CHAR(02),
CHAR(02),
INTEGER,
INTEGER;

DEFINE aBldg CHAR(03);
DEFINE aRecid INTEGER;
DEFINE aYrBlt CHAR(04);
DEFINE aSqft INTEGER;
DEFINE wrkA1 INTEGER;
DEFINE wrkA2 INTEGER;
DEFINE wrkA3 INTEGER;
DEFINE wrkA4 INTEGER;
DEFINE wrkA5 INTEGER;
DEFINE aCode CHAR(18);
DEFINE aQuality CHAR(02);
DEFINE aCondition CHAR(02);
DEFINE aNumSt INTEGER;
DEFINE aPctGood INTEGER;
DEFINE tmpCode CHAR(04);
-- DEFINE acurrent_tax_year INTEGER;

-- added for specific year
-- select current_tax_year into acurrent_tax_year from system_options;

FOREACH
SELECT
'RES',
recid1,
year_built,
main_fn_area,
uppr_fn_area,
unfin_area,
bsmt_area,
bldg_style,
quality,
condition,
num_stories,
phy_pct_good
INTO
aBldg,
aRecid,
aYrBlt,
wrkA1,
wrkA2,
wrkA3,
wrkA4,
tmpCode,
aQuality,
aCondition,
aNumSt,
aPctGood
FROM
residence,
parcel
WHERE parcel.property_id = propid
and parcel.parcel_year = taxyear
-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000
AND parcel.link_id = residence.link_id

IF wrkA1 IS NULL THEN
LET wrkA1 = 0;
END IF;
IF wrkA2 IS NULL THEN
LET wrkA2 = 0;
END IF;
IF wrkA3 IS NULL THEN
LET wrkA3 = 0;
END IF;
IF wrkA4 IS NULL THEN
LET wrkA4 = 0;
END IF;

LET aSqFt = wrkA1 + wrkA2 + wrkA3 + wrkA4;
IF aSqFt = 0 THEN
LET aSqFt = NULL;
END IF;

SELECT
description
INTO
aCode
FROM
value_list
WHERE table_name = 'RESIDENCE'
AND column_name = 'BLDG_STYLE'
AND code = TRIM(tmpCode);

IF aCode is NULL THEN
LET aCode = tmpCode;
END IF;

RETURN
aBldg,
aRecid,
aYrBlt,
aSqFt,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
with RESUME;

END FOREACH;

FOREACH
SELECT
'COM',
recid1,
year_built,
grnd_fl_area,
bldg_type,
quality,
condition,
num_stories,
phy_pct_good
INTO
aBldg,
aRecid,
aYrBlt,
wrkA1,
tmpCode,
aQuality,
aCondition,
aNumSt,
aPctGood
FROM
comm_section,
parcel
WHERE parcel.property_id = propid
-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000
AND parcel.parcel_year = taxyear
AND parcel.link_id = comm_section.link_id

IF wrkA1 IS NULL THEN
LET wrkA1 = 0;
END IF;

LET aSqFt = wrkA1;
IF aSqFt = 0 THEN
LET aSqFt = NULL;
END IF;

SELECT
description
INTO
aCode
FROM
value_list
WHERE table_name = 'COMM_SECTION'
AND column_name = 'BLDG_TYPE'
AND code = TRIM(tmpCode);

IF aCode is NULL THEN
LET aCode = tmpCode;
END IF;

RETURN
aBldg,
aRecid,
aYrBlt,
aSqFt,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
with RESUME;

END FOREACH;

FOREACH
SELECT
'DET',
recid1,
year_built,
main_fn_area,
uppr_fn_area,
structure,
quality,
condition,
num_stories,
phy_pct_good
INTO
aBldg,
aRecid,
aYrBlt,
wrkA1,
wrkA2,
tmpCode,
aQuality,
aCondition,
aNumSt,
aPctGood
FROM
det_structure,
parcel
WHERE parcel.property_id = propid
AND parcel.parcel_year = taxyear
--- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000
AND parcel.link_id = det_structure.link_id

IF wrkA1 IS NULL THEN
LET wrkA1 = 0;
END IF;
IF wrkA2 IS NULL THEN
LET wrkA2 = 0;
END IF;

LET aSqFt = wrkA1 + wrkA2;
IF aSqFt = 0 THEN
LET aSqFt = NULL;
END IF;

SELECT
description
INTO
aCode
FROM
value_list
WHERE table_name = 'DET_STRUCTURE'
AND column_name = 'STRUCTURE'
AND code = TRIM(tmpCode);

IF aCode IS NULL THEN
LET aCode = tmpCode;
END IF;

RETURN
aBldg,
aRecid,
aYrBlt,
aSqFt,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
with RESUME;

END FOREACH;

FOREACH
SELECT
'MOB',
recid1,
year_built,
tot_liv_area,
make,
quality,
condition,
0,
phy_pct_good
INTO
aBldg,
aRecid,
aYrBlt,
wrkA1,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
FROM
mobile_home,
parcel
WHERE parcel.property_id = propid
AND parcel.parcel_year = taxyear
--- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000
AND parcel.link_id = mobile_home.link_id

IF wrkA1 IS NULL THEN
LET wrkA1 = 0;
END IF;

LET aSqFt = wrkA1;
IF aSqFt = 0 THEN
LET aSqFt = NULL;
END IF;
LET aNumSt = NULL;

RETURN
aBldg,
aRecid,
aYrBlt,
aSqFt,
aCode,
aQuality,
aCondition,
aNumSt,
aPctGood
with RESUME;

END FOREACH;

END PROCEDURE;

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-07-31 : 18:39:56

I basically rewrote the "first" foreach statement as:

CREATE PROCEDURE [dbo].[getbldg_x](@propid INTEGER,
@taxyear INTEGER)

AS

DECLARE @aBldg VARCHAR(3)

DECLARE @aRecid INT

DECLARE @aYrBlt VARCHAR(4)

DECLARE @aSqft INT

DECLARE @wrkA1 INT

DECLARE @wrkA2 INT

DECLARE @wrkA3 INT

DECLARE @wrkA4 INT

DECLARE @wrkA5 INT

DECLARE @aCode VARCHAR(18)

DECLARE @tmpCode VARCHAR(4)

DECLARE @aQuality VARCHAR(2)

DECLARE @aCondition VARCHAR(2)

DECLARE @aNumSt INT

DECLARE @aPctGood INT

DECLARE residence_CURSOR CURSOR FOR

SELECT

'RES',

recid1,

year_built,

main_fn_area,

uppr_fn_area,

unfin_area,

bsmt_area,

bldg_style,

quality,

condition,

num_stories,

phy_pct_good

FROM

residence,

parcel

WHERE parcel.property_id = @propid

and parcel.parcel_year = @taxyear

-- AND parcel.parcel_year > 0 and parcel.parcel_year < 3000

AND parcel.link_id = residence.link_id

OPEN residence_CURSOR

FETCH next FROM residence_CURSOR INTO

@aBldg,

@aRecid,

@aYrBlt,

@wrkA1,

@wrkA2,

@wrkA3,

@wrkA4,

@tmpCode,

@aQuality,

@aCondition,

@aNumSt,

@aPctGood

WHILE @@fetch_status = 0

--Processing Statements Begins
BEGIN

IF @wrkA1 IS NULL
SET @wrkA1 = '0'
END

BEGIN

IF @wrkA2 IS NULL
SET @wrkA2 = 0

END

BEGIN

IF @wrkA3 IS NULL
SET @wrkA3 = 0;
END

BEGIN
IF @wrkA4 IS NULL
SET @wrkA4 = 0
END

SET @aSqFt = @wrkA1 + @wrkA2 + @wrkA3 + @wrkA4

BEGIN
IF @aSqFt = 0
SET @aSqFt = NULL
END

--Processing statements End

BEGIN

FETCH next FROM residence_CURSOR INTO

@aBldg,

@aRecid,

@aYrBlt,

@wrkA1,

@wrkA2,

@wrkA3,

@wrkA4,

@tmpCode,

@aQuality,

@aCondition,

@aNumSt,

@aPctGood

END

CLOSE residence_CURSOR

DEALLOCATE residence_CURSOR

I hope this is correct. When I create this procedure in SQL I get no errors. I notice that there is also a "RETURN" statement at the end on my original query. Do you know if I have to do anything with that?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources