Creating an Excel-Based App to send parameters to Stored Procedure in MS SQL Server via VBA

Private Sub ResetButton_Click()
Dim LastColumn As Long
Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim sConnString As String
Application.DisplayAlerts = False

If Sheets("Warehouse View").Cells(3, 1) <> "" Then
LastRow = Sheets("Warehouse View").Cells(Rows.Count, 1).End(xlUp).Row
Range(Sheets("Warehouse View").Cells(3, 1), Sheets("Warehouse View").Cells(LastRow, 6)).ClearContents
Else
MsgBox "No record"
End If

End Sub
Private Sub Get_Data_Click()

Dim conn As ADODB.Connection
Dim iRowNo As Integer
Dim fromFC, toFC, SKU, Units, Carton As Integer
Dim TransReason As String
Dim sConnString As String

sConnString = "Provider=SQLOLEDB;Server=ServerName; Database=DatabseName;Uid=AccountID;Pwd=Passcode;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open sConnString

Set rs = conn.Execute("SELECT * " & _
" FROM [Fulfillment_Rate_Reivew].[SP_WH_View] " & _
" ; ")

If Not rs.EOF Then
Sheets("Warehouse View").Range("A3").CopyFromRecordset rs
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical

End If
conn.Close
Set conn = Nothing

End Sub
Private Sub Refresh_Data_Click()

Dim conn As ADODB.Connection
Dim iRowNo As Integer
Dim fromFC, toFC, SKU, Units, Carton As Integer
Dim TransReason As String
Dim sConnString As String

sConnString = "Provider=SQLOLEDB;Server=ServerName; Database=DatabseName;Uid=AccountID;Pwd=Passcode;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.CommandTimeout = 300
conn.Open sConnString

With Sheets("Warehouse View")
StartDate = .Cells(2, 8)
EndDate = .Cells(2, 9)
conn.Execute "EXEC [Fulfillment_Rate_Reivew].[SP_WH_View] @enddate = " & EndDate & " , @startdate = " & StartDate & ";"
End With

conn.Close
Set conn = Nothing
MsgBox "Refresh Finished"

End Sub
CREATE PROCEDURE [Fulfillment_Rate_Reivew].[SP_WH_View]
(
@enddate AS Int
, @startdate AS Int

)
AS
BEGIN
--DECLARE @startdate AS Int
--DECLARE @enddate AS Int
--Set @startdate = 20191027
--Set @enddate = 20191123
DECLARE @txtStarttime AS VARCHAR(10)
DECLARE @txtEndtime AS VARCHAR(10)
DECLARE @txtStarttimeL AS VARCHAR(10)
DECLARE @txtEndtimeL AS VARCHAR(10)
DECLARE @txtStarttimeSL AS VARCHAR(10)
DECLARE @txtEndtimeSL AS VARCHAR(10)
DECLARE @Length AS VARCHAR(50)
DECLARE @TSQL nVARCHAR(MAX)
DECLARE @TSQL1 nVARCHAR(MAX)
DECLARE @TSQL2 nVARCHAR(MAX)

SET @txtStarttime = CAST(CAST(CAST(@startdate AS BIGINT) AS VARCHAR(10)) AS DATE)
SET @txtEndtime = CAST(CAST(CAST(@enddate AS BIGINT) AS VARCHAR(10)) AS DATE)
SET @Length = cast(@startdate-20000000 AS NVARCHAR(10)) + cast('-' AS VARCHAR(10)) + cast(@enddate-20000000 AS NVARCHAR(10))
SET @txtStarttimeL = DATEADD(day, -28, CAST(CAST(CAST(@startdate AS BIGINT) AS VARCHAR(10)) AS DATE))
SET @txtEndtimeL = DATEADD(day, -28, CAST(CAST(CAST(@enddate AS BIGINT) AS VARCHAR(10)) AS DATE))
SET @txtStarttimeSL = DATEADD(day, -56, CAST(CAST(CAST(@startdate AS BIGINT) AS VARCHAR(10)) AS DATE))
SET @txtEndtimeSL = DATEADD(day, -56, CAST(CAST(CAST(@enddate AS BIGINT) AS VARCHAR(10)) AS DATE))

SET @TSQL =
' Insert INTO [Fulfillment_Rate_Reivew].[SP_WH_View] '+
' (Period, Warehouse, OOS, TOTAL, Fulfillment_Rate , Priority) '+
' Select ''' + @Length + '''As Period, Warehouse, OOS, TOTAL, Fulfillment_Rate, ROW_NUMBER() OVER (ORDER BY Fulfillment_Rate asc) AS [Priority]' +
'from openquery (Teradata_Server_Name, '+
''' Select Warehouse, '+
' SUM(OOS) OOS, '+
' SUM(TOTAL) TOTAL '+
' 1 - CAST(OOS AS DECIMAL(18, 4)) / TOTAL AS Fulfillment_Rate '+
' from DBO.Order_Management_Table'+
' where (ORDER_DATE >= ''''' + @txtStarttime + ''''' And ORDER_DATE <= ''''' + @txtEndtime + ''''')'+
' GROUP BY Warehouse, )T '+
' Group by Warehouse '+
''') a;';
delete [Fulfillment_Rate_Reivew].[SP_WH_View];
EXECUTE sp_executesql @TSQL;
END;
GO

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store