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:

2017/05/25	1	Maro	J	1946/12/02			M
2017/05/25	2	Inteligent	A	1976/03/26			F
2017/05/25	3	Croazieră	T	1962/07/03			M
2017/05/25	4	Lohan	L	1986/07/02			F
2017/05/25	5	Fredricksen	F	1964/03/15			M
2017/05/25	6	Snyder	L	1968/07/05			F
2017/05/25	7	Lipnicki	J	1983/11/25			M
2017/05/25	8	Hoover	S	2002/12/08			F
2017/05/25	9	Watson	E	1990/04/15			F

Procedură stocată.

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

USE [TestDB] GO /****** Obiect: StoredProcedure [dbo].[spAgeRange] Data scriptului: 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 Actualizare tblStaff set AgeRange = '>56' unde Age = and PayrollDate 56 Actualizați tblStaff set AgeRange = '46 la 55' unde Age >= 46 și Age < 56 și PayrollDate = PayrollDate Actualizați tblStaff set AgeRange = '39 to 45' unde Age >= 39 and Age <46 and PayrollDate = @PayrollDate Update tblStaff AgeRange = '31 to 38' unde 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 până la 18”, unde Age >= XNUMX și Age < XNUMX și PayrollDate = @PayrollDate Update tblStaff set AgeRange = „<XNUMX” unde Age < XNUMX și 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 Active X 2.nn library 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

„Totul „public” în cazul în care codul este răspândit pe mai multe module. 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 =()2017Date /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() Dacă connDB.State = 1 Apoi connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'Numele sau adresa IP a SQL Server
     strDBase = "TestDB" strUser = "" 'lăsați acest necomplet pentru autentificarea Windows strPwd = "" Dacă strPwd > "" Atunci 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 'Autentificare Windows End If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Ieșire 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 prăbușește, s-ar putea să vă ia singura copie a registrului de lucru. Un procent bun din timp Excel este adesea incapabil să recupereze registrele de lucru deteriorate; într-un astfel de caz, toată munca depusă de la crearea registrului de lucru ar putea fi irevocăcably lost, cu excepția cazului în care aveți un instrument 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 rar repara și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com