asked on

What is the difference between a VBA function and a SQL Server function?

What is the difference between a VBA private/public function and a SQL Server function?

I'm jumping from Access to SQL Server and VBA to C# sharp now.
In the past when I had an update query I could do the following SQL -

UPDATE tblColTypes SET tblColTypes.Family = Replace_Symbols([Family])
WITH OWNERACCESS OPTION;

And the Replace_Symbols would be in a VBA module as:

Public Function Replace_Symbols(sVlu As String) As Variant
On Error GoTo Err_This

sVlu = Replace(sVlu, "-", "")
sVlu = Replace(sVlu, "/", "")
sVlu = Replace(sVlu, Chr(34), "")

Replace_Symbols = sVlu

Exit_This:
Exit Function

Err_This:
Resume Exit_This
End Function

How would I do this in C# using Visual Studio 2008 - do I create a SQL Server function in under my Server Explorer window?

Guy Hengel [angelIII / a3]

yes, a function would do:

create Function dbo.Replace_Symbols(sVlu as varchar(max) ) returns varchar(max)
as 
begin
 set @sVlu = replace(@sVlu, '-', '')
 set @sVlu = replace(@sVlu, '/', '')
 set @sVlu = replace(@sVlu, '''', '')
 reutrn @sVlu 
End

Open in new window

Guy Hengel [angelIII / a3]

2 notes:

? the functions are created "per database", so make sure you are connected to the correct database when creating the function.
? to use the function in the sql code, you need to prefix with the owner name (dbo.) like in the create:

select dbo.Replace_Symbols('123-456/789''0') data

Open in new window

williamcampbell

I would suggest taking a look at LINQ ... since you are making a fresh start

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

stephenlecomptejr

ASKER

I get an error - Incorrect syntax near 'sVlu'
Must declare the scalar variable "@sVlu"
Must declare the scalar variable "@sVlu"
Must declare the scalar variable "@sVlu"
ok

I'm doing this inside of Visual Studio 2008 - under Server Explorer - under Database.mdf - Add New Functions...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stephenlecomptejr

ASKER

Yea - Thank you sincerely!