在过程中使用多脉冲变量

Question

所以这对你来说应该是非常基础的。我有一个通过日期进行查找的存储过程。我想添加一个@storeno变量，它可以是一个sores，也可以是所有sores。我该怎么做呢？

USE [POS_REPORT]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_ReportTotals] (
@fromdate VARCHAR(10),
@todate VARCHAR(10)
)
AS
BEGIN
SELECT
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] AS 'pos',
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] AS 'ecc',
CASE WHEN [POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] = [POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] THEN 'BALANCED' ELSE 'UNBALANCED' END AS 'status',
[POS_REPORT].[dbo].[comments].[responce],
[POS_REPORT].[dbo].[comments].[comment]
FROM
[POS_REPORT].[dbo].[Txn_Daily_Totals]
LEFT JOIN
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS] ON
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[storeno] +
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[transactiondate]
LEFT JOIN
[POS_REPORT].[dbo].[comments] ON 
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[comments].[storeno] +
[POS_REPORT].[dbo].[comments].[transactiondate]
WHERE
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]  = @fromdate AND
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] <= @todate
ORDER BY
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]
END

GO

任何帮助都将不胜感激。

Answer 1

我假设storeno是整数，即使不是整数，你也可以对任何其他数据类型遵循同样的原则。这在语法上是很简单的方法，但是要注意执行计划：

CREATE PROCEDURE [dbo].[sp_ReportTotals] (
@fromdate VARCHAR(10),
@todate VARCHAR(10),
@storeno INT -- if you want all stores send NULL as @storeno parameter, if you want specific store send its storeno
)
AS
BEGIN
SELECT
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] AS 'pos',
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] AS 'ecc',
CASE WHEN [POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] = [POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] THEN 'BALANCED' ELSE 'UNBALANCED' END AS 'status',
[POS_REPORT].[dbo].[comments].[responce],
[POS_REPORT].[dbo].[comments].[comment]
FROM
[POS_REPORT].[dbo].[Txn_Daily_Totals]
LEFT JOIN
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS] ON
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[storeno] +
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[transactiondate]
LEFT JOIN
[POS_REPORT].[dbo].[comments] ON 
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[comments].[storeno] +
[POS_REPORT].[dbo].[comments].[transactiondate]
WHERE
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]  = @fromdate AND
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] <= @todate AND
(@storeno IS NULL OR [POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] = @storeno)
ORDER BY
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]
END

另一种可能产生更有利的执行计划的方法可能是：

CREATE PROCEDURE [dbo].[sp_ReportTotals] (
@fromdate VARCHAR(10),
@todate VARCHAR(10),
@storeno INT -- if you want all stores send NULL as @storeno parameter, if you want specific store send its storeno
)
AS
BEGIN
SELECT
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] AS 'pos',
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] AS 'ecc',
CASE WHEN [POS_REPORT].[dbo].[Txn_Daily_Totals].[amount] = [POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[amount] THEN 'BALANCED' ELSE 'UNBALANCED' END AS 'status',
[POS_REPORT].[dbo].[comments].[responce],
[POS_REPORT].[dbo].[comments].[comment]
INTO #temp
FROM
[POS_REPORT].[dbo].[Txn_Daily_Totals]
LEFT JOIN
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS] ON
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[storeno] +
[POS_REPORT].[dbo].[SAP_FI_INBOUND_DAILY_TOTALS].[transactiondate]
LEFT JOIN
[POS_REPORT].[dbo].[comments] ON 
[POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] +
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] =
[POS_REPORT].[dbo].[comments].[storeno] +
[POS_REPORT].[dbo].[comments].[transactiondate]
WHERE
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]  = @fromdate AND
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] <= @todate 
ORDER BY
[POS_REPORT].[dbo].[Txn_Daily_Totals].[Description],
[POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]

IF(@storeno IS NULL)
BEGIN
    SELECT  *
    FROM    #temp   
END
ELSE
BEGIN
    SELECT  *
    FROM    #temp
    WHERE   storeno = @storeno
END

END

这种方法包括两个SELECT语句和一个临时表，但它可能执行得更快(即可能有更好的执行计划)。在您的SP的生产版本中使用这些方法之前，请检查执行计划和查询成本指标，并确定哪种方法更适合您。

Answer 2

CREATE PROCEDURE [dbo].[sp_ReportTotals] (
--your paramets
@storeno your_data_type
....
--Changed WHERE clause
WHERE
  [POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate]  = @fromdate AND
  [POS_REPORT].[dbo].[Txn_Daily_Totals].[transactiondate] <= @todate AND
  [POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno] = ISNULL(@storeno, [POS_REPORT].[dbo].[Txn_Daily_Totals].[storeno])