How can I call a VB function from SQL Server SP?

Hi
I need to use the VB function "strconv" in my SP.
Is it possible?
Can anyone help?
Best regards
David LauWhy? What's so special about that particular string that converting it can't
be done in T-SQL, yet you decided it should be done on the server?
The sp_OA* procedures let you invoke ActiveX components from SQL, but there
are several downsides to this. The two worst IMHO are: possible negative
impact on server stability, and performance issues.
ML
http://milambda.blogspot.com/|||David L. wrote:

> Hi
> I need to use the VB function "strconv" in my SP.
> Is it possible?
> Can anyone help?
> Best regards
> David Lau
AFAIK all the features of strconv are supported by native TSQL
equivalents. It would therefore be simpler and much more efficient to
use the TSQL version rather than make an external call to VB. Take a
look at the string functions in SQL Server Books Online.
The answer to the question in your subject depends on the version of
SQL Server you are using. In SQL 2000 your options are limited to COM
automation using the sp_OA procs or making a call via an extended proc.
Usually it would be better to utilize client-side code or TSQL.
In 2005 you have the option of putting .NET code directly in a proc.
David Portas
SQL Server MVP
--|||don't do that
"David L." <DavidL@.discussions.microsoft.com> wrote in message
news:A3447E24-AD32-42D0-A834-9C8977D5596C@.microsoft.com...
> Hi
> I need to use the VB function "strconv" in my SP.
> Is it possible?
> Can anyone help?
> Best regards
> David Lau|||Hello David L.,

> I need to use the VB function "strconv" in my SP.
If you're just doing UpperCase/LowerCase, there's the Upper() and Lower()
functions in T-SQL. If your wanting ProperCase and you're using SQL2005,
you can wrap the function with SQLCLR, ala:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StringConversionFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function ProperCase(ByVal Source As SqlString) As SqlString
Return New SqlString(StrConv(Source.Value, VbStrConv.ProperCase))
End Function
End Class
Call it via:
select 'Proper',dbo.ProperCase('ThE QUick BROWN fox jumpEd oVeR thE laZY
DOG')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thank you Kent and everyone who has replied this email.
Let's say I would like to do the Narrow for Japanese charactors.
Is there any similar function in TSQL?
Best regards
David
"Kent Tegels" wrote:

> Hello David L.,
>
> If you're just doing UpperCase/LowerCase, there's the Upper() and Lower()
> functions in T-SQL. If your wanting ProperCase and you're using SQL2005,
> you can wrap the function with SQLCLR, ala:
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
> Imports System.Data.SqlTypes
> Imports Microsoft.SqlServer.Server
> Partial Public Class StringConversionFunctions
> <Microsoft.SqlServer.Server.SqlFunction()> _
> Public Shared Function ProperCase(ByVal Source As SqlString) As SqlStr
ing
> Return New SqlString(StrConv(Source.Value, VbStrConv.ProperCase))
> End Function
> End Class
> Call it via:
> select 'Proper',dbo.ProperCase('ThE QUick BROWN fox jumpEd oVeR thE laZY
> DOG')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello David L.,

> Thank you Kent and everyone who has replied this email.
> Let's say I would like to do the Narrow for Japanese charactors.
> Is there any similar function in TSQL?
Not that I'm immediately aware since it's not really clear to me what Narrow
actually does. Is it something like this?
declare @.m1 nvarchar(50)
set @.m1 = N'? 150 ?'
select cast(@.m1 as varchar(50))
Either way, you should still be able to use the code previously shown with
a different enumeration value.
Domo,
Kentsql