VB Equivalent Format function for T-SQL?

Question

Post reply

VB Equivalent Format function for T-SQL?

dimp

SSC Veteran

Points: 217
More actions
January 19, 2004 at 3:52 pm

#106857

Has anyone gone to the arduous process of writing a proc or UDF which mimics VB's Format$() function.

Or does anyone know if its possible to access the VBE6.dll via an extended proc (this is where VBA's Format() function lives) ... (as much as I dislike using extended procs on production systems)?

Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dimp SSC Veteran Points: 217 More actions · Answer 1

This is sad isn't it .... replying to my own post...

but I just thought that I could access other MS Office apps via OLE Automation and access their format functions. For example I just wrote a proc to access Excel's Text() function and it works fine. But again I don't like having to depend on whether an Excel runtime or Office for that matter exists on a server!

Does anyone have a better idea? Surely someone has found CAST, STR, CONVERT etc way too limiting but wanted to perform such operations within a stored proc ...

gljjr SSCrazy Eights Points: 9970 More actions · Answer 2

I would submit that the Format function belongs to the front end app and not in SQL. The front end application is the only app that truely knows how the data needs to be displayed. It is the job of SQL Server to supply that data.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

dimp SSC Veteran Points: 217 More actions · Answer 3

Gary,

I completely agree! But just trust me when I say that (although I don't need it) I can't depend on this type of formatting to go on in any other layer than that of the server. I have some back end business rules which require this type of formatting and also the production systems I'm working with are very remote and any traffic between client and server (this is a non-web based app) needs to be kept to a minimum.

Just assume for the moment that the front end cannot be changed but the back end can.

Jim

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I doubt that using OLE automation is really that what you're after.

Although you might be able to achieve the goal you're after, using the sp_oa* procedures has massive impact on security.

Think of what someone might be able to do with the FileSystemObject!

Don't know if you can do the same, but I told my CFO that if he wants to have the numbers in blue with five decimals instead of green with 2 decimals, I am able to provide him the raw data in *.csv format which he can format the whole day long. That lead to some interesting discussions, but after a while this educational process worked.

So when you say the front end cannot be changed at the moment, maybe you can provide such an alternative?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Off-hand, I can't think of anything that you can format with Format$() that you can't format in T-SQL using the built-in functions. Maybe if you were more specific about what you are trying to format and to what.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]