incorrect syntax in a while loop

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

SQL Server 2000 Forums

Transact-SQL (2000)

incorrect syntax in a while loop

Author

Topic

gobuff12
Starting Member

1 Post

Posted - 2008-05-05 : 13:29:32

In the below code, I get the following error message during the exec @statement line:
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'IW'.

I can't figure out what is wrong with that line. Any help would be appreciated.

declare @ntwk_cgv varchar (250),
@ntwk_feature varchar (250),
@statement varchar (8000)

declare number_cursor cursor
for
select distinct ntwk_cgv, ntwk_feature
from NM

open number_cursor

fetch next from number_cursor
into @ntwk_cgv, @ntwk_feature

while @@fetch_status = 0
begin
set @statement = '
insert into BillingDetail
select *
from openquery(prod_r, '''+
'select distinct ca.customer_acct_id, ca.customer_name, ca.customer_type,
ca.status, ca.asn_bill_cycl_id, pa.product_account_id,
pa.product_acct_name, pa.product_code, pacg.component_group_cd,
pacg.component_grp_val, pac.prcmp_code, pac.bill_eff_bgn_date,
pac.bill_eff_end_date, pac.billed_thru_date, pac.install_order_id,
pac.install_date, pu.first_usage_dt, pu.last_usage_dt,
ct.customer_type_desc, ap.terminate_method
from customer_acct ca, prd_acct_cmp pac, prd_acct_cmp_grp pacg,
product_acct pa, pacg_usage pu, customer_type ct, all_800_profile ap
where ca.customer_acct_id = pa.customer_acct_id
and pa.product_account_id = pac.product_account_id
and pac.product_account_id = pacg.product_account_id
and pac.occurrence_number = pacg.occurrence_number
and pacg.component_group_cd = pu.component_group_cd(+)
and pacg.component_grp_val = pu.component_grp_val(+)
and ca.customer_type = ct.customer_type
and pacg.component_grp_val = ap.phone_number
and pa.product_account_id = ap.product_account_id
and pa.customer_acct_id = ap.customer_acct_id
and (pacg.component_group_cd = ''IW''
and pacg.component_grp_val = ''' + @ntwk_cgv + ''')
'')'

print @statement
exec (@statement)

fetch next from number_cursor
into @ntwk_cgv, @ntwk_feature

end
close number_cursor
deallocate number_cursor

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 13:49:07

Why are you using a cursor? It only makes things more difficult and processes more slowly.

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 13:50:44

Also try to learn the ANSI way of writing joins

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 14:06:55

How about siomething like this?

DECLARE #Table1 TABLE (ntwk_cgv varchar(250))

insert into #Table1
select distinct ntwk_cgv
from NM

Then create a #table2 to insert all the values from your open query using the same logic except for the pacg.component_grp_val piece in the where clause(using proper join syntax of course).

Then

insert into BillingDetail
select B.*
From #table1 A
join #table2 B on A.ntwk_cgv = B.component_grp_val

Of course a lot of this hinges on the type of database being used by your openquery. Is it SQL server too? How many records do you expect to come back before filtering on component_grp_val?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources