Create Stored Procedure Using VBA Macros
Sub CreateStoredProcedureUsingADODB_With_Variables()
Establish_connection_With_SQLDB
Dim SqlQuery As String
SqlQuery = "Create procedure Sp_Sales_With_Variables" & vbNewLine
SqlQuery = SqlQuery & "@@Loc Varchar(11)" & vbNewLine
SqlQuery = SqlQuery & "as" & vbNewLine
SqlQuery = SqlQuery & "Select * from Sales where Location = @@Loc"
'MsgBox SqlQuery
Con.Execute SqlQuery
Dim Sql As String
Sql = "Exec Sp_Sales_With_Variables '" & Sheets("InputData").Range("I5").Value & "'"
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Rs.Open Sql, Con
Dim Newsh As Worksheet
Set Newsh = ThisWorkbook.Sheets.Add(after:=Sheets(Sheets.Count))
Newsh.Range("A2").CopyFromRecordset Rs
Con.Execute "drop procedure Sp_Sales_With_Variables"
Con.Close
Newsh.Name = Sheets("InputData").Range("I5").Value & " Data"
End Sub