Kaip paskambinti a SQL Server Išsaugota procedūra iš Excel VBA

Duomenis serveryje galima modifikuoti „Excel VBA“ ištyrus įrašus „kliento pusėje“, juos pakeičiant pagal poreikį ir išsaugant atgal į serverį.
Veiksmingesnis būdas tai padaryti, ypač jei duomenų bazė yra atokioje vietoje ir yra didelis srautas, yra atlikti darbą „serverio pusėje“. Šis pratimas iškviečia išsaugotą procedūrą iš „Excel“, kad suskirstytų darbuotojus į amžiaus grupes pagal jų gimimo datas (ty 18–25 m., 26–35 m. ir t. t.), be gausaus duomenų mainų tarp serverio ir „Excel“.

Šiame straipsnyje daroma prielaida, kad skaitytojas turi rodomą kūrėjo juostelę ir yra susipažinęs su VBA redaktoriumi. Jei ne, naudokite Google „Excel Developer Tab“ arba „Excel Code Window“.

Pratimą sudaro trys elementai:

• Duomenų lentelė tblPersonalas duomenų bazėje TestDB;
• Išsaugota procedūra spAgeRange;
• Excel xlsm, kurį vadinsime xlsm. Galima rasti Excel failo pavyzdį čia

duomenų lentelė

Sukurkite duomenų bazę SQL Server vadinamas DBTest.

Nustatykite šiuos lentelės stulpelius tblPersonalas.

Nukopijuokite į lentelę:

Išsaugota procedūra.

Paleiskite šį scenarijų su TestDB, kad sukurtumėte saugomą procedūrą:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

Išsaugota procedūra bus išsaugota duomenų bazės skiltyje „Programavimas“.

„Excel VBA“

Belieka iškviesti išsaugotą procedūrą iš „Excel“, pateikiant Darbo užmokesčio data parametras „2017/05/25“. Pastebėsite, kad aš tiesiog įvedžiau duomenis  Darbo užmokesčio data kaip styga, o ne grumtis su įvairiais datos formatais. Pakankamai paprasta konvertuoti eilutę į datą naudojant Konvertuoti funkcija, jei Darbo užmokesčio data turi būti naudojamas aritmetiniais tikslais.

Sukurkite naują darbaknygę. Atidarykite VBA kodo langą ir įdėkite modulį.

Kodo lango meniu Įrankiai nurodykite atitinkamą „Active X 2.nn“ biblioteka palengvinti duomenų objektų naudojimą.

Įklijuokite šį kodą į kodo langą. Tai suaktyvinus, bus prisijungta prie SQL Server, kaip nurodyta „ConnectDatabase“ antrinėje procedūroje

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Pridėkite mygtuką prie 1 lapo ir priskirkite jį prie procedūros „WriteStoredProcedure"

Rezultatai

Paspauskite mygtuką, tada patikrinkite tblStaff, kuris turėtų būti atnaujintas pagal amžių ir amžiaus intervalus. Apdorojimas įvyko serverio pusėje.

Sugadintų darbo knygų atkūrimas

Jei „Excel“ programa sugestų, kartu gali būti prarasta ir vienintelė darbaknygės kopija. Nemaža dalis atvejų, kai „Excel“ negali atkurti sugadintų darbaknygių; tokiu atveju visas darbas, atliktas nuo darbaknygės sukūrimo, gali būti negrįžtamai prarastas, nebent turite įrankį, kuris padėtų... taisyti Excel xlsx arba xlsm failus.

Autoriaus įvadas:

Felixas Hookeris yra duomenų atkūrimo ekspertas DataNumen, Inc., kuri yra pasaulyje duomenų atkūrimo technologijų lyderė, įskaitant rar failų taisymas ir sql atkūrimo programinės įrangos produktai. Norėdami gauti daugiau informacijos, apsilankykite WWW.datanumen.com