VBA function works in form but not in queries

[JEM]

I am converting an Access 2003 app to an ADP/SQL Server app. The
application was created by someone else. There are several forms,
queries and reports that call a VBA function that contatenates a
person's name in a presentable format (it deals with whether they have
a prefix, suffix, etc. )using a CASE SELECT statement. This function
works fine after the upsize in forms, but does not work correctly in
the udfs or sprocs. Can a function be called from a udf or sproc or
should i rewrite it another way and what would the best way be? Thanks
for any help!

JEM

 

[Sylvain Lafontaine]

VBA functions cannot be called in UDF or SP because the former are running
on the client side and the later on the server side.

The obvious solutions would be to apply the VBA function to the resultset
returned by SQL-Server or to create an UDF function that will duplicate its
functionality.

If the manipulation is not to heavy, a third possibility would be simply to
add a Case statement directly into the Select statement. See the BOL for
more details on the Case statement.

 

[JEM]

That makes sense. Thanks!

 

[aaron.kempf]

you could just use coalesce or nullif
COALESCE(lastname + ', ' + firstname, lastname, firstname)

this will give last, first and then last and then first.

but in the realworld; dont use coalesce for anything; use nullif.. i
got fired from a job once for using coalesce; i mean-- these idiots
didnt understand the diff between measures and members and i'll be
damned if i start indexing measures

 

[heying]

I am converting an Access 2003 app to an ADP/SQL Server app. The
application was created by someone else. There are several forms,
queries and reports that call a VBA function that contatenates a
person's name in a presentable format (it deals with whether they have
a prefix, suffix, etc. )using a CASE SELECT statement. This function
works fine after the upsize in forms, but does not work correctly in
the udfs or sprocs. Can a function be called from a udf or sproc or
should i rewrite it another way and what would the best way be? Thanks
for any help!

JEM