Changing a VBA function into Transact SQL

[LeanneGodney]

Hi everyone,

I'm fumbling through converting my db to SQL server backend and ADP frontend. So far so good (am managing to change from DAO to ADO), but the Transact SQL is still a bit of a bother...

I have this vba function that I need to run as a stored procedure or function or something, so that it's run locally on the server. I've tried! But what I've created doesn't work, and I'm not sure why!

I'm understanding that you use BEGIN and END when you want a few lines of code to run after an if statement has tested true or false... But it's still not working!

Any help would be GREAAATLY appreciated. :0)

Leanne

Code:

CREATE FUNCTION [dbo].[PipelineOrderFLP] (@TheStatusID int, @TheTradeDate nvarchar(7), @TheProbability int, @TheIssueDate datetime, @TheEffectiveDate datetime, @TheOrder varchar)

RETURNS INT

BEGIN
IF @TheStatusID <> 9 and TheTradeStatusID <> 4
BEGIN
IF @theTradeDate = null
set @TheTradeDate = convert(varchar,@TheEffectiveDate)

set @TheTradeDate = convert(varchar,datepart(yy, @TheTradeDate)) + '-' + convert(varchar,datepart(mm, @TheTradeDate))

IF @TheTradeDate = convert(nvarchar(4),getdate(),12)
BEGIN
IF @TheStatusID = 3
set @TheOrder = 2
ELSE if @TheStatusID = 6
set @TheOrder = 1
ELSE if @TheProbability = 1
set @TheOrder = 3
END
ELSE IF @TheTradeDate = datepart(yy, getdate()) + "-" + datepart(mm, getdate())
BEGIN
IF @TheStatusID = 3
set @TheOrder = 4
ELSE IF @TheProbability = 1
set @TheOrder=5
END
ELSE IF @TheTradeDate = datepart(yy, dateadd(m,2,getdate()) + "-" + datepart(mm, dateadd(m,2,getdate())
BEGIN
IF @TheStatusID = 3
set @TheOrder = 6
ELSE IF @TheProbabiilty = 1
set @TheOrder = 7
END

IF @TheOrder =""
set @TheOrder = 9
END

RETURN @TheOrder

END

 

[LeanneGodney]

Hi Again,

This is another VBA function that I need to have working in SQL Server. Any help would be appreciated:

----

Function AddWorkingDays(TheDate, NoOfDays, SubtractOrAdd)
On Error GoTo CodeExit

If IsNull(TheDate) = False And IsNull(NoOfDays) = False Then
DaysAdded = 0
WorkingDate = TheDate

Do
If SubtractOrAdd = "Subtract" Then
WorkingDate = WorkingDate - 1
Else
WorkingDate = DateValue(WorkingDate) + 1
End If

Select Case Format(WorkingDate, "ddd")
Case Is = "Mon", "Tue", "Wed", "Thu", "Fri"
DaysAdded = DaysAdded + 1
Case Else
DaysAdded = DaysAdded
End Select

Loop Until DaysAdded = NoOfDays

AddWorkingDays = WorkingDate
Else
AddWorkingDays = Null
End If

CodeExit:
Exit Function

End Function