Moving a VBA function to SQL server

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:
"."
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"),
".")
If Additional = True Then AddCode = Nz(DLookup("AdditionalCode",
"Settings"), "A")

TmpHeader = Replace(TmpHeader, "", BudgetType)
TmpHeader = Replace(TmpHeader, "", Format(JobNo, "0000"))
TmpHeader = Replace(TmpHeader, "", Format(RunningNo, "00"))
TmpHeader = Replace(TmpHeader, "", AddCode)
TmpHeader = Replace(TmpHeader, "", Nz(AccountNo, ""))
TmpHeader = Replace(TmpHeader, "", VesselRef)
TmpHeader = Replace(TmpHeader, "", 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.

Regards
Erik Rudbeck

Re: Moving a VBA function to SQL server

On Sun, 20 Apr 2008 00:44:30 +0200, "Erik Rudbeck"
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:
>"."
>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"),
>".")
>If Additional = True Then AddCode = Nz(DLookup("AdditionalCode",
>"Settings"), "A")
>
>TmpHeader = Replace(TmpHeader, "", BudgetType)
>TmpHeader = Replace(TmpHeader, "", Format(JobNo, "0000"))
>TmpHeader = Replace(TmpHeader, "", Format(RunningNo, "00"))
>TmpHeader = Replace(TmpHeader, "", AddCode)
>TmpHeader = Replace(TmpHeader, "", Nz(AccountNo, ""))
>TmpHeader = Replace(TmpHeader, "", VesselRef)
>TmpHeader = Replace(TmpHeader, "", 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.
>
>Regards
>Erik Rudbeck
>

Re: Moving a VBA function to SQL server

"Tom van Stiphout" wrote in message
news:qadl04985jn4nt2r4am4up3le6bl5906nc@4ax.com...
> On Sun, 20 Apr 2008 00:44:30 +0200, "Erik Rudbeck"
> 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:
>>"."
>>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"),
>>".")
>>If Additional = True Then AddCode = Nz(DLookup("AdditionalCode",
>>"Settings"), "A")
>>
>>TmpHeader = Replace(TmpHeader, "", BudgetType)
>>TmpHeader = Replace(TmpHeader, "", Format(JobNo, "0000"))
>>TmpHeader = Replace(TmpHeader, "", Format(RunningNo, "00"))
>>TmpHeader = Replace(TmpHeader, "", AddCode)
>>TmpHeader = Replace(TmpHeader, "", Nz(AccountNo, ""))
>>TmpHeader = Replace(TmpHeader, "", VesselRef)
>>TmpHeader = Replace(TmpHeader, "", 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

Re: Moving a VBA function to SQL server

On Sun, 20 Apr 2008 12:26:14 +0200, "Erik Rudbeck"
wrote:

If optimized is important, do not put procedural code in your T-SQL
procedures. SQL is a set-based language.

I can think of a third option: have the query return the raw data
columns, and format the jobheader in your VBA code.

-Tom.


>
>"Tom van Stiphout" wrote in message
>news:qadl04985jn4nt2r4am4up3le6bl5906nc@4ax.com...
>> On Sun, 20 Apr 2008 00:44:30 +0200, "Erik Rudbeck"
>> 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:
>>>"."
>>>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"),
>>>".")
>>>If Additional = True Then AddCode = Nz(DLookup("AdditionalCode",
>>>"Settings"), "A")
>>>
>>>TmpHeader = Replace(TmpHeader, "", BudgetType)
>>>TmpHeader = Replace(TmpHeader, "", Format(JobNo, "0000"))
>>>TmpHeader = Replace(TmpHeader, "", Format(RunningNo, "00"))
>>>TmpHeader = Replace(TmpHeader, "", AddCode)
>>>TmpHeader = Replace(TmpHeader, "", Nz(AccountNo, ""))
>>>TmpHeader = Replace(TmpHeader, "", VesselRef)
>>>TmpHeader = Replace(TmpHeader, "", 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
>
>
>
>

Re: Moving a VBA function to SQL server

Tom van Stiphout wrote in
news:kllq04p489upg2bo4slm82v6obgkptb1cf@4ax.com:

> On Sun, 20 Apr 2008 12:26:14 +0200, "Erik Rudbeck"
> wrote:
>
> If optimized is important, do not put procedural code in your T-SQL
> procedures. SQL is a set-based language.
>
> I can think of a third option: have the query return the raw data
> columns, and format the jobheader in your VBA code.
>
> -Tom.



I think there is nothing inherently more efficient or sophisticated in
using SQL udfs or cursors than using VBA, regardless of the snob appeal
for doing so.

It’s likely to be faster for many users to be running VBA procedures and
scanning record sets, each on his/her own workstation with its own CPU
(maybe two or four) than for many users to be running SQL functions and
scanning through cursors all on the same server, unless the record sets
are large enough that time for bringing them over the wire is a factor,
and this often depends on the wire.

Sometimes there are constructions that just seem simpler in SQL, and I
find this particularly so when doing aggregates of aggregates ... of
aggregates, or writing script that alters or creates procedures, views or
functions.

Of course, one may want to keep all the data-centric things in SQL. And
the more that is kept there, the smaller our front end can be and that’s
likely to make it easy to distribute (although clients might say “$16000
for 800 kilobytes? Are you nuts?”)

One must be very careful about writing procedures in SQL that deal with
numbers other than integers. We may think that 1000/ 3 = 333.333.... but
SQL will return 333 unless we explicitly require 1000 to be typed as
float or small money or whatever. Dates can have similar problems. And
NULLs can bite much harder than in VBA where error messages seem to pop
up much more readily than from a server 3000 miles away. If you're going
to write T-SQL with numerical calculations I recommend brushing up on
Convert or Cast beforehand.

Re: Moving a VBA function to SQL server

On Tue, 22 Apr 2008 03:52:13 GMT, lyle fairfield
wrote:

Maybe the OP can implement it both ways and report back to us.
It occurred to me that his jobheader is really more a matter of
presentation, so my thinking moves away from the data tier.
It's a good thing cursors are not needed for his solution because they
are exceptionally slow.

-Tom.


>Tom van Stiphout wrote in
>news:kllq04p489upg2bo4slm82v6obgkptb1cf@4ax.com:
>
>> On Sun, 20 Apr 2008 12:26:14 +0200, "Erik Rudbeck"
>> wrote:
>>
>> If optimized is important, do not put procedural code in your T-SQL
>> procedures. SQL is a set-based language.
>>
>> I can think of a third option: have the query return the raw data
>> columns, and format the jobheader in your VBA code.
>>
>> -Tom.
>
>
>
>I think there is nothing inherently more efficient or sophisticated in
>using SQL udfs or cursors than using VBA, regardless of the snob appeal
>for doing so.
>
>It’s likely to be faster for many users to be running VBA procedures and
>scanning record sets, each on his/her own workstation with its own CPU
>(maybe two or four) than for many users to be running SQL functions and
>scanning through cursors all on the same server, unless the record sets
>are large enough that time for bringing them over the wire is a factor,
>and this often depends on the wire.
>
>Sometimes there are constructions that just seem simpler in SQL, and I
>find this particularly so when doing aggregates of aggregates ... of
>aggregates, or writing script that alters or creates procedures, views or
>functions.
>
>Of course, one may want to keep all the data-centric things in SQL. And
>the more that is kept there, the smaller our front end can be and that’s
>likely to make it easy to distribute (although clients might say “$16000
>for 800 kilobytes? Are you nuts?”)
>
>One must be very careful about writing procedures in SQL that deal with
>numbers other than integers. We may think that 1000/ 3 = 333.333.... but
>SQL will return 333 unless we explicitly require 1000 to be typed as
>float or small money or whatever. Dates can have similar problems. And
>NULLs can bite much harder than in VBA where error messages seem to pop
>up much more readily than from a server 3000 miles away. If you're going
>to write T-SQL with numerical calculations I recommend brushing up on
>Convert or Cast beforehand.
>
>
>
>
>