We have a GUI in VFP that get it's data from a web service (C#). The web service use a dynamic SQL or call a UDF in SQL Server to return the data.

I agree that putting SQL code in the business layer is not the most portable way of doing things. I'm not familiar enough with SQL Server to know how to write a dynamic query in T-SQL. However, I've come across a great article (http://www.sommarskog.se/dynamic_sql.html) that gave me some good ideas and good arguments for going the dynamic stored procedures way.

>May I ask, what's the intention for your application?
>
>The reason I ask is that I'm developing an application in VFP9 where VFP9 is simply the GUI, and all of my SQL procedures are stored within SQL Server, thus allowing other platforms/tiers to access them too, i.e. web-site or VB.
>
>Going down the stored procedure route means that it's far easier to developed a truly tiered application that DOESN'T depend on any one aspect, i.e. the VFP, VB or web-site. Also, as the procedures are stored within SQL, and called from the various components, it's easier to track/modify/amend code than if it were stored in the native formats.
>
>The downside is that there's a great deal more work involved, not least of all that I've had to learn SQL Server to a reasonable standard, but clearly the benefits are obvious. If you can invest the time, and find the enthusiasm to do it this way then (and it's only my personal opinion) I'm sure you'll find it to be worthwhile.