Solved

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

Posted on 2009-03-30
6
260 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:stephenlecomptejr
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24023786
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24023802
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

0
 
LVL 12

Expert Comment

by:williamcampbell
ID: 24023806
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


0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 24023869
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...
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24023931
sorry:
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

0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 31564568
Yea - Thank you sincerely!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now