Convert VBA Code to SQL sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert VBA Code to SQL sp

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 11:53:02

I'd like to convert this vba code from access to run in sql 2000 and I'm a newbie to vba and am not sure how to do this. can I get the same functionality from an sp?
' Global SYSTEM Variables...
Global GBL_SystemName As String
Global GBL_CommonPath As String
Global GBL_WorkPath As String
Global GBL_DataUpdatePath As String
Global GBL_ArchivePath As String
Global GBL_LiveMarsPath As String
Global GBL_MARS_DSN_Name As String
Global GBL_MARS_DB_Server As String
Global GBL_MARS_DB_Name As String
Global GBL_RawData_DSN_Name As String
Global GBL_RawData_DB_Server As String
Global GBL_RawData_DB_Name As String
' Global CLIENT Variables...
Global GBL_ClientName As String
Global GBL_ClientAbbrev As String
Global GBL_MedisunEligibilityAge As Long
Global GBL_DoctorsManualEntryFlag As String
Global GBL_LastUpdateDate As Date
Global GBL_LastInvoiceGTEDate As Date
Global GBL_LastInvoiceLTEDate As Date
Global GBL_NewInvoiceGTEDate As Date
Global GBL_NewInvoiceLTEDate As Date

Public Function Get_Globals(G_name As String)
Select Case UCase(G_name)
Case UCase("SystemName")
Get_Globals = GBL_SystemName
Case UCase("ClientName")
Get_Globals = GBL_ClientName
Case UCase("ClientAbbrev")
Get_Globals = GBL_ClientAbbrev
Case UCase("MedisunEligibilityAge")
Get_Globals = GBL_MedisunEligibilityAge
Case UCase("LastUpdateDate")
Get_Globals = GBL_LastUpdateDate
Case UCase("LastInvoiceGTEDate")
Get_Globals = GBL_LastInvoiceGTEDate
Case UCase("LastInvoiceLTEDate")
Get_Globals = GBL_LastInvoiceLTEDate
Case UCase("NewInvoiceGTEDate")
Get_Globals = GBL_NewInvoiceGTEDate
Case UCase("NewInvoiceLTEDate")
Get_Globals = GBL_NewInvoiceLTEDate
Case UCase("WorkPath")
Get_Globals = GBL_WorkPath
Case UCase("DataUpdatePath")
Get_Globals = GBL_DataUpdatePath
Case UCase("ArchivePath")
Get_Globals = GBL_ArchivePath
Case UCase("LiveMarsPath")
Get_Globals = GBL_LiveMarsPath
Case UCase("CommonPath")
Get_Globals = GBL_CommonPath
Case UCase("MARS_DSN_Name")
Get_Globals = GBL_MARS_DSN_Name
Case UCase("MARS_DB_Server")
Get_Globals = GBL_MARS_DB_Server
Case UCase("MARS_DB_Name")
Get_Globals = GBL_MARS_DB_Name
Case UCase("RawData_DSN_Name")
Get_Globals = GBL_RawData_DSN_Name
Case UCase("RawData_DB_Server")
Get_Globals = GBL_RawData_DB_Server
Case UCase("RawData_DB_Name")
Get_Globals = GBL_RawData_DB_Name
Case UCase("DoctorsManualEntryFlag")
Get_Globals = GBL_DoctorsManualEntryFlag
End Select
End Function

Public Sub GetProperties(strPropertyTblName As String, Optional strClientCode As String)
' Create database connection and recordset...
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "select * from tln_" & strPropertyTblName & "Properties"
If UCase(Trim(strPropertyTblName)) = "CLIENT" Then
strSQL = strSQL & " where CPClientCode = '" & strClientCode & "'"
End If
rs.Open strSQL, con, 1 ' 1 = adOpenKeyset
If Not rs.EOF Then
Do While Not rs.EOF
Select Case UCase(Trim(rs("PropertyName")))
Case UCase("MARS_DSN_Name")
GBL_MARS_DSN_Name = rs("PropertyValue")
Case UCase("MARS_DB_Server")
GBL_MARS_DB_Server = rs("PropertyValue")
Case UCase("MARS_DB_Name")
GBL_MARS_DB_Name = rs("PropertyValue")
Case UCase("RawData_DSN_Name")
GBL_RawData_DSN_Name = rs("PropertyValue")
Case UCase("RawData_DB_Server")
GBL_RawData_DB_Server = rs("PropertyValue")
Case UCase("RawData_DB_Name")
GBL_RawData_DB_Name = rs("PropertyValue")
Case UCase("SystemName")
GBL_SystemName = rs("PropertyValue")
Case UCase("ClientName")
GBL_ClientName = rs("PropertyValue")
Case UCase("ClientAbbrev")
GBL_ClientAbbrev = rs("PropertyValue")
Case UCase("MedisunEligibilityAge")
GBL_MedisunEligibilityAge = rs("PropertyValue")
Case UCase("WorkPath")
GBL_WorkPath = rs("PropertyValue")
If Right(Trim(GBL_WorkPath), 1) <> "\" Then
GBL_WorkPath = GBL_WorkPath & "\"
End If
Case UCase("DataUpdatePath")
GBL_DataUpdatePath = rs("PropertyValue")
If Right(Trim(GBL_DataUpdatePath), 1) <> "\" Then
GBL_DataUpdatePath = GBL_DataUpdatePath & "\"
End If
Case UCase("ArchivePath")
GBL_ArchivePath = rs("PropertyValue")
If Right(Trim(GBL_ArchivePath), 1) <> "\" Then
GBL_ArchivePath = GBL_ArchivePath & "\"
End If
Case UCase("LiveMarsPath")
GBL_LiveMarsPath = rs("PropertyValue")
If Right(Trim(GBL_LiveMarsPath), 1) <> "\" Then
GBL_LiveMarsPath = GBL_LiveMarsPath & "\"
End If
Case UCase("CommonPath")
GBL_CommonPath = rs("PropertyValue")
If Right(Trim(GBL_CommonPath), 1) <> "\" Then
GBL_CommonPath = GBL_CommonPath & "\"
End If
Case UCase("DoctorsManualEntryFlag")
GBL_DoctorsManualEntryFlag = UCase(rs("PropertyValue"))
If Trim(GBL_DoctorsManualEntryFlag) = "" Then
GBL_DoctorsManualEntryFlag = "Y"
End If
Case Else
' Invalid property... do nothing...
End Select
rs.MoveNext
Loop
End If
rs.Close

Set rs = Nothing
Set con = Nothing
End Sub

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-10 : 12:52:24

OK, small bit of advice

1). Do you know what the code is suppose to do
2). If yes, then tell us in Business terms
3). If no, then how can we help

If you read the hint link in my sig, then you'll see what we need to help you

Good Luck

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 15:56:00

ok, sorry for the messy post. I'm going to try something different with this and see if I can come up with better results. I'd like to create a function without input parameters that returns the database name to be called from sp's later. I'm getting the error, "Select statements included within a function cannot return data to a client" from this:
CREATE FUNCTION fn_GetClientCode()
RETURNS nvarchar (6)
AS
BEGIN
DECLARE @GetClientCode nvarchar(6)
select [name] from master.dbo.[sysdatabases]

return @GetClientCode
end

what am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:04:23

How is @GetClientCode populated?

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 16:07:54

I need the database name to populate it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:10:21

How do you know which database to pick for @GetClientCode?

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 16:14:29

this will be used in every db so I don't want to hard code a db name. can it be populated by pulling the db name it is currently in? is there a link between sysdatabases and current system db tables to pull the db name?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 16:36:07

There is a built in function, DB_NAME(), that returns the name of the current database, if that is all you are after.

I can't say you have really explained what your function is supposed to do.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:42:10

[code]CREATE PROCEDURE dbo.uspMyProcedure
(
@PropertyTableName SYSNAME,
@ClientCode VARCHAR(200)
)
AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000)

IF @PropertyTblName = 'Client'
SET @SQL = 'SELECT * FROM tln_ClientProperties WHERE CpClientCode = ' + QUOTENAME(@ClientCode, '''')
ELSE
SET @SQL = 'SELECT * FROM tln_' + @PropertyTblName + 'Properties'

EXEC sp_executesql @SQL[/code]

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 16:46:07

I've been asked to create a function that returns the current db name to be called as a variable by other sps. Does that make any sense? I'm a little confused on how to do it myself, but I was hoping someone out here has done something like that.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 16:49:32

quote:
Originally posted by smorty44

I've been asked to create a function that returns the current db name to be called as a variable by other sps. Does that make any sense? I'm a little confused on how to do it myself, but I was hoping someone out here has done something like that.

As I said before, there is already a built-in function, DB_NAME(), that returns the current database name. Just use that.

CODO ERGO SUM

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 16:52:44

I'll tell my boss that. ;-)

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-04-10 : 17:48:44

Thank you for your help today guys. I got some results using both your suggestions. Here's the function I created to capture the current db name and can use it in sps.

create FUNCTION fn_GetClientCode()
RETURNS nvarchar (6)
AS
BEGIN

return (select db_name())

end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-10 : 20:50:36

I really do not understand the point of that function, unless you are trying to cause an error when the database name is longer than 6 characters. All it is doing is calling another built-in function. There is no reason why the application couldn't use the DB_NAME() function directly.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources