基于私有框架的SQL Server Web应用：带参存储过程替代视图咨询

Hey Marius, let's tackle your two main questions here—how to use your parameterized stored procedure with your framework's table component, and whether you can integrate it into a view.

1. Replacing the View with Your Parameterized Logic #

Since your framework accepts views as data sources but your stored procedure needs a parameter, the cleanest solution is to convert your stored procedure into an inline table-valued function (TVF). TVFs act like views but support parameters, so your framework can query them just like it would a view, with the added ability to pass the OrderID you need.

Here's how to rewrite your stored procedure as an inline TVF:

CREATE FUNCTION dbo.ufn_Sales_OrdersLinesProductsByID (@OrderId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        ol.OrderID, 
        ol.Created, 
        ol.CreatedBy, 
        ol.Updated, 
        ol.UpdatedBy, 
        ol.CUT, 
        ol.CDL, 
        ol.Domain, 
        ol.ProductID, 
        ol.Amount, 
        p.ProductName, 
        p.Supplier, 
        p.Quantity AS TotalQuantity, 
        p.Price, 
        ol.PrimKey 
    FROM dbo.atbl_Sales_OrdersLines AS ol 
    INNER JOIN dbo.atbl_Sales_Products AS p ON ol.ProductID = p.ProductID 
    WHERE ol.OrderID = @OrderId
)

To use this with your framework:

• Instead of pointing the table component to a view, point it to this function. Depending on your framework's API, you'll need to pass the current open order's OrderID as a parameter when querying the function. For example, in code, you'd execute something like:

  SELECT * FROM dbo.ufn_Sales_OrdersLinesProductsByID(@CurrentOpenOrderId)
  

If your framework has specific support for stored procedures (even though you mentioned it uses views), check its internal docs for how to bind parameters to a stored procedure call. But the TVF approach is more aligned with the "view-like" data object your framework expects.

2. Can You Integrate the Stored Procedure into a View? #

Short answer: No, you can't directly integrate a parameterized stored procedure into a view—views are static, parameterless queries by design. But there are workarounds, though most are not ideal for production due to concurrency risks.

Hacky Workaround (Not Recommended): Session Context

You could use SQL Server's SESSION_CONTEXT to pass a parameter to a view, but this can cause issues if multiple users/requests share the same session (like in connection pooling):

1. Create a view that reads from the session context:

   CREATE VIEW dbo.vw_Sales_OrdersLinesProducts
   AS
   SELECT 
       ol.OrderID, 
       ol.Created, 
       ol.CreatedBy, 
       ol.Updated, 
       ol.UpdatedBy, 
       ol.CUT, 
       ol.CDL, 
       ol.Domain, 
       ol.ProductID, 
       ol.Amount, 
       p.ProductName, 
       p.Supplier, 
       p.Quantity AS TotalQuantity, 
       p.Price, 
       ol.PrimKey 
   FROM dbo.atbl_Sales_OrdersLines AS ol 
   INNER JOIN dbo.atbl_Sales_Products AS p ON ol.ProductID = p.ProductID 
   WHERE ol.OrderID = CAST(SESSION_CONTEXT(N'CurrentOrderID') AS INT)
   

2. Before querying the view, set the session context with your OrderID:

   EXEC sp_set_session_context @key = N'CurrentOrderID', @value = @YourOrderID;
   

3. Query the view normally—it will return only the rows for the OrderID you set.

Again, this is risky because if the session is reused for another request without resetting the context, you'll get incorrect data. The table-valued function is the far safer and cleaner option.

Wrap-up: Convert your stored procedure to an inline table-valued function. It's the most straightforward way to get parameterized, view-like behavior that works with your framework.

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