请求协助将MSSQL存储过程调用转换为Laravel查询

No worries, let's break this down step by step. In Laravel, you can use the DB facade to execute your MSSQL stored procedure and capture the return value just like in your original MSSQL code.

Here's how to do it:

First, ensure your Laravel database connection is configured for MSSQL (this is typically set up in config/database.php). Then use the following code to call the procedure and retrieve the return value:

use Illuminate\Support\Facades\DB;

// Define your parameters (replace with dynamic values from requests/other sources as needed)
$customerId = 12;
$customerWorkshopId = 12;
$groupId = 32;
$productId = 34;
$storageCardId = -1;
$orderId = -1;
$groupCode = 'OP570';
$productCode = 'A000000034';
$manufacturerCode = 'OP570';
$supplierCode = 'A000000034';
$brand = 'FILTRON';
$description = 'Oil filter';
$orderedQuantity = 8;
$pricePerPiece = 6.743;
$pricePerPieceWithVAT = 8.29;
$purchasePricePerPiece = 5.348;
$purchasePricePerPieceWithVAT = 6.58;
$totalPrice = 53.94;
$totalPriceWithVAT = 66.32;
$totalPurchasePrice = 42.78;
$totalPurchasePriceWithVAT = 52.64;
$discountInPercent = 20.7;
$discountPrice = -11.16;
$surchargesPrice = 0;
$surchargePriceWithVAT = 0;
$pricePerPieceInCurrency = 6.740;
$pricePerPieceWithVATInCurrency = 8.290;
$purchasePricePerPieceInCurrency = 5.350;
$purchasePricePerPieceWithVATInCurrency = 6.580;
$totalPriceInCurrency = 53.920;
$totalPriceWithVATInCurrency = 66.320;
$totalPurchasePriceInCurrency = 42.800;
$totalPurchasePriceWithVATInCurrency = 52.640;
$discountPriceInCurrency = -1.390;
$surchargesPriceInCurrency = 0;
$surchargesPriceWithVATInCurrency = 0;
$currencyId = 16034;
$foreignCurrencyId = 16034;
$note = '';
$vatRate = 23.00;
$historyInfo = '';
$actionPrice = 0;
$childPricePerPieceInCurrency = -1.000;
$childPricePerPieceWithVATInCurrency = -1.000;
$childPurchasePricePerPieceInCurrency = -1.000;
$childPurchasePricePerPieceWithVATInCurrency = -1.000;
$childDiscountInCurrency = -1.000;

// Execute the stored procedure and get the return value
$result = DB::selectOne("
    DECLARE @return_value int;
    EXEC @return_value = [API_CreateShopBasket]
        @CustomerID = ?,
        @CustomerWorkshopID = ?,
        @GroupID = ?,
        @ProductID = ?,
        @StorageCardID = ?,
        @OrderID = ?,
        @GroupCode = ?,
        @ProductCode = ?,
        @ManufacturerCode = ?,
        @SupplierCode = ?,
        @Brand = ?,
        @Desription = ?, -- Note: Original parameter has a typo (Desription instead of Description)
        @OrderedQuantity = ?,
        @PricePerPiece = ?,
        @PricePerPieceWithVAT = ?,
        @PurchasePricePerPiece = ?,
        @PurchasePricePerPieceWithVAT = ?,
        @TotalPrice = ?,
        @TotalPriceWithVAT = ?,
        @TotalPurchasePrice = ?,
        @TotalPurchasePriceWithVAT = ?,
        @DiscountInPercent = ?,
        @DiscountPrice = ?,
        @SurchargesPrice = ?,
        @SurchargePriceWithVAT = ?,
        @PricePerPieceInCurrency = ?,
        @PricePerPieceWithVATInCurrency = ?,
        @PurchasePricePerPieceInCurrency = ?,
        @PurchasePricePerPieceWithVATInCurrency = ?,
        @TotalPriceInCurrency = ?,
        @TotalPriceWithVATInCurrency = ?,
        @TotalPurchasePriceInCurrency = ?,
        @TotalPurchasePriceWithVATInCurrency = ?,
        @DiscountPriceInCurrency = ?,
        @SurchargesPriceInCurrency = ?,
        @SurchargesPriceWithVATInCurrency = ?,
        @CurrencyID = ?,
        @ForeignCurrencyID = ?,
        @Note = ?,
        @VATRate = ?,
        @HistoryInfo = ?,
        @ActionPrice = ?,
        @ChildPricePerPieceInCurrency = ?,
        @ChildPricePerPieceWithVATInCurrency = ?,
        @ChildPurchasePricePerPieceInCurrency = ?,
        @ChildPurchasePricePerPieceWithVATInCurrency = ?,
        @ChildDiscountInCurrency = ?;
    SELECT @return_value AS return_value;
", [
    $customerId,
    $customerWorkshopId,
    $groupId,
    $productId,
    $storageCardId,
    $orderId,
    $groupCode,
    $productCode,
    $manufacturerCode,
    $supplierCode,
    $brand,
    $description,
    $orderedQuantity,
    $pricePerPiece,
    $pricePerPieceWithVAT,
    $purchasePricePerPiece,
    $purchasePricePerPieceWithVAT,
    $totalPrice,
    $totalPriceWithVAT,
    $totalPurchasePrice,
    $totalPurchasePriceWithVAT,
    $discountInPercent,
    $discountPrice,
    $surchargesPrice,
    $surchargePriceWithVAT,
    $pricePerPieceInCurrency,
    $pricePerPieceWithVATInCurrency,
    $purchasePricePerPieceInCurrency,
    $purchasePricePerPieceWithVATInCurrency,
    $totalPriceInCurrency,
    $totalPriceWithVATInCurrency,
    $totalPurchasePriceInCurrency,
    $totalPurchasePriceWithVATInCurrency,
    $discountPriceInCurrency,
    $surchargesPriceInCurrency,
    $surchargesPriceWithVATInCurrency,
    $currencyId,
    $foreignCurrencyId,
    $note,
    $vatRate,
    $historyInfo,
    $actionPrice,
    $childPricePerPieceInCurrency,
    $childPricePerPieceWithVATInCurrency,
    $childPurchasePricePerPieceInCurrency,
    $childPurchasePricePerPieceWithVATInCurrency,
    $childDiscountInCurrency
]);

// Access the return value
$returnValue = $result->return_value;

Key Details: #

• DB::selectOne() is used because we expect a single result (the procedure's return value).
• The ? placeholders securely bind parameters to prevent SQL injection—ensure the order of variables in the array matches the order of placeholders in the query.
• I kept the typo @Desription from your original code to match the stored procedure, but double-check if this is intentional or a mistake in the procedure itself.
• Replace hardcoded variables with dynamic values (e.g., $request->input('customer_id')) based on where your data comes from.

内容的提问来源于stack exchange，提问作者pribor