Cum să sunați a SQL Server Procedură stocată din Excel VBA

Datele de pe un server pot fi modificate examinând înregistrările „partea client” în Excel VBA, modificându-le după cum este necesar și salvându-le înapoi pe server.
O modalitate mai eficientă de a face acest lucru, în special dacă baza de date se află într-o locație îndepărtată și există o mulțime de trafic implicat, este să faceți lucrul „pe partea de server”. Acest exercițiu apelează la o procedură stocată din Excel pentru a clasifica angajații în intervale de vârstă în funcție de datele lor de naștere (adică 18-25 de ani, 26-35 de ani etc.), fără un schimb copios de date între server și Excel.

Acest articol presupune că cititorul are afișată panglica pentru dezvoltatori și este familiarizat cu Editorul VBA. Dacă nu, vă rugăm să Google „Fila Dezvoltator Excel” sau „Fereastra Cod Excel”.

Există trei elemente ale exercițiului:

• Un tabel de date tblStaff în cadrul unei baze de date TestDB;
• O procedură stocată sAgeRange;
• Un Excel xlsm, pe care îl vom numi xlsm. Un exemplu de fișier Excel poate fi găsit aici

Tabel de date

Creați o bază de date în SQL Server denumit DBTest.

Configurați următoarele coloane pentru un tabel tblStaff.

Copiați următoarele în tabel:

Procedură stocată.

Rulați acest script împotriva TestDB pentru a crea procedura stocată:

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

Procedura stocată va fi salvată în „Programabilitate” în baza de date.

Excel VBA

Tot ce rămâne este să apelați procedura stocată din Excel, furnizând PayrollDate parametrul „2017/05/25”. Veți observa că am introdus pur și simplu date  PayrollDate ca un șir, mai degrabă decât să lupte cu diferite formate de dată. Este suficient de simplu să convertiți un șir într-o dată folosind Converti funcționează dacă PayrollDate urmează a fi folosit în scopuri aritmetice.

Creați un nou registru de lucru. Deschideți fereastra de cod VBA și introduceți un modul.

Din meniul Instrumente al ferestrei de cod, faceți referire la cea corespunzătoare Bibliotecă Active X 2.nn pentru a facilita utilizarea obiectelor de date.

Lipiți următorul cod în fereastra Cod. Acesta, odată activat, se va conecta la SQL Server, conform procedurii secundare ConnectDatabase

'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

Adăugați un buton la Sheet1 și atribuiți-l la subprocedura „WriteStoredProcedureMatei 22:21

Rezultatele

Apăsați butonul, apoi examinați tblStaff, care ar trebui actualizat cu vârstele și intervalele de vârstă. Prelucrarea a avut loc pe partea de server.

Recuperarea registrelor de lucru corupte

În cazul în care Excel se blochează, este posibil să piardă singura copie a registrului de lucru. În majoritatea cazurilor, Excel nu poate recupera registrele de lucru deteriorate; într-un astfel de caz, toată munca depusă de la crearea registrului de lucru s-ar putea pierde irevocabil, cu excepția cazului în care aveți un instrument pentru a... repara Excel fișiere xlsx sau xlsm.

Introducerea autorului:

Felix Hooker este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv reparare rar și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com