Dynamic SQL

[pft]

Is there a size limit to a dynamic select statment. For some reason it is not building the entire statement.

"select " + @report_title + " as report_title, "+
"ac.tail_no, at.aur_mod_des, at.fy, ta.asset_desc, at.last_dd250_dt, act.activity_name, rk.remarks, ac.cur_cond_cd, av.nalc, " +
"at.maint_due_dt, ac.cont_del_dt as cont_del_dt, act.short_name "+
"from aur ac, cmp..aur at, type_asset ta, activity act, cmp..aur_variant av, remarks rk "+
"Where ac.tail_no = at.tail_no and ac.cur_type_asset = ta.type_asset and ac.loc_cd = act.loc_cd "+
"and ac.tail_no *= rk.tail_no and at.aur_mod_des = av.aur_mod_des and at.act_inv='Y' and at.country_cd='US' and rk.closeout_dt > getdate() "+
"and av.test_sfx not in ('J', 'X') and not exists(select tail_no from cmp..aur_prgs where tail_no = ac.tail_no) "+
@whereclause + " order by ac.tail_no"

 

[Tim1]

Hiya,

I cannot immediately see anything wrong with your clause, and I have used dynamic SQL 3x or 4x the length that you have there.

Exactly what error message are you getting? Perhaps that may help see what is wrong.

Tim

 

[pft]

I got it figured out. The error was in the @whereclause that I was building in a loop. DbArtisian was sending me back an eronious error message that made me think my statement was to large. Thanks for the help anyway.

 

[Tim1]

Ahhh

DBArtisan. Say no more!!

Wonderful tool,

another frustrated DBArtisan user!

Tim