Re: Moving a VBA function to SQL server


"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message
news:qadl04985jn4nt2r4am4up3le6bl5906nc@xxxxxxxxxx
On Sun, 20 Apr 2008 00:44:30 +0200, "Erik Rudbeck" <no_mails@xxxxxxxx>
wrote:

Two options:
* If you're on SQL Server 2005 (you didn't mention this important
nugget) you can use a .NET assembly to implement procedural code.
That's what I would probably do.
* If you want to do this using the T-SQL language, study the topic
"String Functions (Transact-SQL) " in Books Online. That's what you
have to work with when it comes to string manipulations. You'll find
that a REPLACE function is available, and Nz can be simulated with
ISNULL.

-Tom.


I have a VBA function that I would like to move to SQL Server

The SQL server database contains a table named 'WorkOrders'.
Each work order gets a job header that is derived/concatenated from several
fields in 'WorkOrders'.

Example:
WorkOrders contains the following fields (among others):
BudgetType (nvarchar,1): D
JobNo: (int): 123
RunningNo (int): 2
Additional (bit): 1

This will produce a job header formatted like this: D0123.02A

The trailing 'A' is only set when the Additional bit is 1. If the Additional
bit is 0, then the formatted job header should be: D0123.02

Now the tricky part (to me) is that the application allows the user to
specify how he wants the job header formatted:

Using place holders he can specify a string like:
"<BudgetType><JobNo>.<RunningNo><Additional>"
This would produce the formatting shown in above example.
The format he specifies is stored in table 'Settings' - field 'JobHdrMask'

I already have a VBA function that will take all parameters that can go into
a job header, and look up the preferred format string in table 'Settings',
carry out the formatting, and finally return the formatted job header as a
string:

This VBA function reads like this:
First please note:
The function allows for more place holders than in above example
The character 'A' to use when the 'Additional' bit is true is also user
defined, and looked up in table 'Settings'
The JobNo element must return leading zeros to always make it 4 charaters
The RunningNo element must return leading zeros to always make it 2
charaters

Here goes:

-----
Public Function FormatJobHdr(Optional BudgetType As String = "",_
Optional JobNo As Variant = "",_
Optional RunningNo As Variant = "",_
Optional Additional As Boolean = False,_
Optional AccountNo As Variant = "",_
Optional VesselRef As String = "",_
Optional VesselIMO As Variant = "") As String

Dim TmpHeader As String, AddCode As String

TmpHeader = Nz(DLookup("JobHdrMask", "Settings"),
"<BudgetType><JobNo>.<RunningNo><Additional>")
If Additional = True Then AddCode = Nz(DLookup("AdditionalCode",
"Settings"), "A")

TmpHeader = Replace(TmpHeader, "<BudgetType>", BudgetType)
TmpHeader = Replace(TmpHeader, "<JobNo>", Format(JobNo, "0000"))
TmpHeader = Replace(TmpHeader, "<RunningNo>", Format(RunningNo, "00"))
TmpHeader = Replace(TmpHeader, "<Additional>", AddCode)
TmpHeader = Replace(TmpHeader, "<AccountNo>", Nz(AccountNo, ""))
TmpHeader = Replace(TmpHeader, "<VesselRef>", VesselRef)
TmpHeader = Replace(TmpHeader, "<VesselIMO>", VesselIMO)

FormatJobHdr = TmpHeader
-----

This same functionality I would like to implement on the SQL server, so that
views can be made to return work orders with already formattet job headers.
I guess a Stored Procedure is what is required, so that I can use this
procedure in several views.

I'm new to SQL server, so if anybody could assist in cracking this nut for
me, I would be very gratefull.


Thanks Tom,

I'm using SQL server 2000, which then means T-SQL ?

I worked quite a while trying to make a stored procedure do the job, but as I am a novice at this I
tend to generate more questions than answers.

- How should I declare the optional input parameters.
- How do I handle missing parameters correctly?
- Is there an equivalent to the VBA variant data type on SQL server, and if so, should I use it, and
does it behave as I'm used to?
- How do I most easily add leading zeros? - I'm missing the VBA format function here
- Do I need the procedure to look up JobHdrMask and AdditionalCode on every call, or would another
approach be more efficient?
- How do I ensure that the Trailing 'A' for additional is only included when supposed to?
- Should the code blindly replace all valid place holders as in my current VBA code, or should it
try to replace only the place holders contained in JobHdrMask?
- Is a stored procedure the best way to go with this?

Optimized code is important here, because the procedure is potentionally called for +10000 records
in 'WorkOrders'. Then again, perhaps this is nothing to SQL server.

These questions are probably tedious for SQL experts, but I'm not one of those - yet...
I was hoping for a specific example on how to solve this issue. I would learn a lot from such an
example, that would otherwise take a long time. I need an overall understanding of stored procedures
and T-SQL in order to feel confident that the procedure I create is an optimized one, and not a
beginners clumsy attempt.

Best regards
Erik





.

Relevant Pages

  • Re: The third time to ask in here ... Mobile Web form
    ... You would be better directing sql server questions to the ... > i can't change its format, ... > format in the DB to string or the string that the user ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Moving a VBA function to SQL server
    ... "String Functions " in Books Online. ... The SQL server database contains a table named 'WorkOrders'. ... This will produce a job header formatted like this: ... This would produce the formatting shown in above example. ...
    (comp.databases.ms-access)
  • Re: Undocumented CONVERT styles
    ... The string value will not be interpreted ... independently of the date format: ... SQL Server changes this to something else when you add it ...
    (microsoft.public.sqlserver.programming)
  • Re: Format a datetime columns output?
    ... you could convert the date to a string? ... SQL Server you should use the format YYYYMMDD, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Moving a VBA function to SQL server
    ... SQL is a set-based language. ... "String Functions " in Books Online. ... This will produce a job header formatted like this: ... The format he specifies is stored in table 'Settings' - field 'JobHdrMask' ...
    (comp.databases.ms-access)