როგორ დავურეკოთ ა SQL Server შენახული პროცედურა Excel VBA-დან

სერვერზე მონაცემები შეიძლება შეიცვალოს Excel VBA-ში ჩანაწერების „კლიენტის მხარის“ შემოწმებით, საჭიროებისამებრ შეცვლით და სერვერზე დაბრუნებით.
ამის გაკეთების უფრო ეფექტური გზა, განსაკუთრებით იმ შემთხვევაში, თუ მონაცემთა ბაზა დისტანციურ ადგილას მდებარეობს და ბევრი ტრეფიკია ჩართული, არის სამუშაოს შესრულება "სერვერის მხარეს". ეს სავარჯიშო მოითხოვს Excel-ის შენახულ პროცედურას თანამშრომლების ასაკობრივ დიაპაზონებად დაყოფისთვის მათი დაბადების თარიღების მიხედვით (მაგ. 18-25 წელი, 26-35 წელი და ა.შ.), სერვერსა და Excel-ს შორის მონაცემთა უხვი გაცვლის გარეშე.

ეს სტატია ვარაუდობს, რომ მკითხველს აქვს დეველოპერის ლენტი ნაჩვენები და იცნობს VBA რედაქტორს. თუ არა, გთხოვთ Google „Excel Developer Tab“ ან „Excel Code Window“.

სავარჯიშოში სამი ელემენტია:

• მონაცემთა ცხრილი tblStaff მონაცემთა ბაზაში TestDB;
• შენახული პროცედურა spAgeRange;
• Excel xlsm, რომელსაც ჩვენ დავარქმევთ xlsm. შეგიძლიათ იპოვოთ Excel ფაილის ნიმუში აქ დაწკაპუნებით

მონაცემთა ცხრილი

შექმენით მონაცემთა ბაზა SQL Server მოუწოდა DBTest.

დააყენეთ შემდეგი სვეტები ცხრილისთვის tblStaff.

დააკოპირეთ შემდეგი ცხრილში:

შენახვის პროცედურა.

გაუშვით ეს სკრიპტი TestDB-ის წინააღმდეგ შენახული პროცედურის შესაქმნელად:

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

შენახული პროცედურა შეინახება მონაცემთა ბაზაში „პროგრამირებადობის“ ქვეშ.

Excel VBA

რჩება მხოლოდ Excel-დან შენახული პროცედურის გამოძახება, მოწოდებით სახელფასო თარიღი პარამეტრი "2017/05/25". თქვენ შეამჩნევთ, რომ მე უბრალოდ მონაცემები მაქვს აკრეფილი  სახელფასო თარიღი როგორც სტრიქონი, ვიდრე ეჭიდება თარიღის სხვადასხვა ფორმატებს. სტრიქონის თარიღად გადაქცევა საკმარისად მარტივია გადაიყვანეთ ფუნქცია თუ სახელფასო თარიღი გამოიყენება არითმეტიკული მიზნებისთვის.

შექმენით ახალი სამუშაო წიგნი. გახსენით VBA კოდის ფანჯარა და ჩადეთ მოდული.

კოდის ფანჯრის ინსტრუმენტების მენიუდან, მიუთითეთ შესაბამისი Active X 2.nn ბიბლიოთეკა მონაცემთა ობიექტების გამოყენების გასაადვილებლად.

ჩასვით შემდეგი კოდი კოდის ფანჯარაში. ეს, გააქტიურების შემდეგ, დაუკავშირდება SQL Server, 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

დაამატეთ ღილაკი Sheet1-ს და მიანიშნეთ იგი ქვეპროცედურზე “WriteStoredProcedure"

შედეგები

დააჭირეთ ღილაკს, შემდეგ შეამოწმეთ tblStaff, რომელიც უნდა განახლდეს ასაკისა და ასაკობრივი დიაპაზონის მიხედვით. დამუშავება მოხდა სერვერის მხარეს.

დაზიანებული სამუშაო წიგნების აღდგენა

თუ Excel-ი გაითიშება, შესაძლოა, მასთან ერთად სამუშაო წიგნის თქვენი ერთადერთი ასლიც წაიშალოს. შემთხვევათა დიდ ნაწილში Excel ხშირად ვერ ახერხებს დაზიანებული სამუშაო წიგნების აღდგენას; ასეთ შემთხვევაში, სამუშაო წიგნის შექმნის შემდეგ შესრულებული ყველა სამუშაო შეიძლება შეუქცევადად დაიკარგოს, თუ არ გაქვთ ინსტრუმენტი, რომელიც... Excel-ის შეკეთება xlsx ან xlsm ფაილები.

ავტორი შესავალი:

ფელიქს ჰუკერი არის მონაცემთა აღდგენის ექსპერტი DataNumen, Inc., რომელიც მსოფლიო ლიდერია მონაცემთა აღდგენის ტექნოლოგიებში, მათ შორის rar-ის შეკეთება და sql აღდგენის პროგრამული პროდუქტები. დამატებითი ინფორმაციისთვის ეწვიეთ www.datanumen. ერთად