我有以下程式：
CREATE PROCEDURE sp_types
@type varchar(100)
AS
BEGIN
DECLARE @products table(productId int)
IF @type = 'Merchandise'
BEGIN
INSERT INTO @products
SELECT productId
FROM dbo.product
WHERE type = @type
END
ELSE IF @type = 'Electronics'
BEGIN
INSERT INTO @products
SELECT productId
FROM dbo.product
WHERE type = @type
END
ELSE IF @type = 'Home'
BEGIN
INSERT INTO @products
SELECT productId
FROM dbo.product
WHERE type = @type
END
ELSE
BEGIN
INSERT INTO @products
SELECT productId
FROM dbo.product
WHERE type = @type
END
/* here we have logic to convert all the productids in the @products table into an XML format
<products>
<productId>1</productId>
<productId>2</productId>
....
....
....
<productId>100</productId>
</products>
*/
/* after we get the XML string, it is passed to another procedure to print out details about the products */
EXEC sp_products_list @xml = @productXml
END /* procedure ends here */
這是sp_products_list程式：
CREATE PROCEDURE sp_products_list
@xml XML
AS
BEGIN
DECLARE @products TABLE (productId int)
INSERT INTO @products
SELECT @xml.value('productId','int')
FROM @xml.nodes('products')
/* Select statement */
SELECT
a.productId, a.productName,
b.productRegion, b.ProductQuantity,
c.productSupplier
FROM
products a
JOIN
productRegion b ON a.productid = b.productid
JOIN
productSupplier c ON c.productRegion = b.productRegion
WHERE
a.productId IN (SELECT productId FROM @products)
END /* procedure end */
該sp_products_list由比其他許多其他程式稱為sp_types程式。我有一個要求，其中，當我通過型別“商品”向sp_types程序，然后我需要一些額外的列等productSupplierRegion，productSupplierCount等顯示。
但是對于其余的型別，我只需要顯示sp_products_list程序中的 select 陳述句當前顯示的內容。
如果我只是將我需要的列添加到當前sp_products_list程序中的 select 陳述句中，那么它們將針對傳遞給sp_types程序的任何型別顯示，這不是我想要的。
My solution: one of the solutions I could think of was receiving a @type variable as an input in the sp_products_list procedure and having an if-else statement for the select statement. If the type of 'Merchandise' is passed in then display the select with additional columns otherwise display the regular columns.
The problem I might face in the future with this approach is what if we want to add different types of columns for different @type variables that are passed in. In that case, I'll have to have to do multiple if-else statements for each type. I was planning on using dynamic SQL but my idea was shot down since my team is not a huge fan of dynamic SQL.
Now I'm trying to find a robust solution for this problem that might work in any scenario. Any thoughts or suggestions? Thank you!
uj5u.com熱心網友回復：
我建議您重寫sp_products_list為表值函式，然后您可以將它與您需要的任何額外列連接起來
CREATE OR ALTER FUNCTION dbo.products_list ( @productId int )
RETURNS TABLE
AS RETURN
SELECT
p.productId, p.productName,
pr.productRegion, pr.ProductQuantity,
ps.productSupplier
FROM
products p
JOIN
productRegion pr ON p.productid = pr.productid
JOIN
productSupplier ps ON ps.productRegion = pr.productRegion
WHERE
p.productId = @productId;
GO
然后您只需在每個程序中使用該函式。例如。
CREATE PROCEDURE ProductByType
@type varchar(100)
AS
IF @type = 'Merchandise'
BEGIN
SELECT pl.*, p.OtherColumns, t.OtherTablesColumns
FROM dbo.product p
CROSS APPLY dbo.products_list (p.productId) pl
LEFT JOIN OtherTable t ON SomeCondition
WHERE p.type = @type;
END;
ELSE
BEGIN
SELECT pl.*
FROM dbo.product p
CROSS APPLY dbo.products_list (p.productId) pl
WHERE p.type = @type;
END;
您可能會發現將@type選擇推送到函式中會更高效。然后你可以簡單地做
SELECT pl.*
FROM dbo.products_list_by_type (@type) pl
服務器將從查詢計劃中洗掉所有不需要的列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374194.html