存储过程关键编号示例
设置创建简单关键编号的程序相当简单。
第一步是在“关键编号”表单中设置您的关键编号。
- 为您的自定义存储过程选择一个程序名称。我们建议您为自己的自定义程序创建命名规范，使它们不会与当前或未来的程序冲突；使用包括您公司名称的前缀是执行此项的一种方法。
- 选择您希望能够设置的参数，但不更改代码中的值。仔细计划参数定义可以使同一程序也用于您希望检索的多个关键编号。例如，您可设置特定的买家标识或针对三个不同的仓库运行同一程序。
- 记住，您可以在您的代码中覆盖此表单上的任何和所有设置。
接下来，打开查询分析器或您的首选编辑器，创建您的自定义存储过程。每个关键编号的参数都是相同的，需要与以下内容相似（其中，SSSWBCanCoBookSp 是您的程序的名称）：
CREATE PROCEDURE [dbo].[SSSWBCanCoBookSp] (
@KPINum WBKPINumType
, @AsOfDate DateType
, @Amount AmountType OUTPUT
, @Parm1 WBSourceNameType = NULL
, @Parm2 WBSourceNameType = NULL
, @Parm3 WBSourceNameType = NULL
, @Parm4 WBSourceNameType = NULL
, @Parm5 WBSourceNameType = NULL
, @Parm6 WBSourceNameType = NULL
, @Parm7 WBSourceNameType = NULL
, @Parm8 WBSourceNameType = NULL
, @Parm9 WBSourceNameType = NULL
, @Parm10 WBSourceNameType = NULL
, @Parm11 WBSourceNameType = NULL
, @Parm12 WBSourceNameType = NULL
, @Parm13 WBSourceNameType = NULL
, @Parm14 WBSourceNameType = NULL
, @Parm15 WBSourceNameType = NULL
, @Parm16 WBSourceNameType = NULL
, @Parm17 WBSourceNameType = NULL
, @Parm18 WBSourceNameType = NULL
, @Parm19 WBSourceNameType = NULL
, @Parm20 WBSourceNameType = NULL
, @Parm21 WBSourceNameType = NULL
, @Parm22 WBSourceNameType = NULL
, @Parm23 WBSourceNameType = NULL
, @Parm24 WBSourceNameType = NULL
, @Parm25 WBSourceNameType = NULL
, @Parm26 WBSourceNameType = NULL
, @Parm27 WBSourceNameType = NULL
, @Parm28 WBSourceNameType = NULL
, @Parm29 WBSourceNameType = NULL
, @Parm30 WBSourceNameType = NULL
, @Parm31 WBSourceNameType = NULL
, @Parm32 WBSourceNameType = NULL
, @Parm33 WBSourceNameType = NULL
, @Parm34 WBSourceNameType = NULL
, @Parm35 WBSourceNameType = NULL
, @Parm36 WBSourceNameType = NULL
, @Parm37 WBSourceNameType = NULL
, @Parm38 WBSourceNameType = NULL
, @Parm39 WBSourceNameType = NULL
, @Parm40 WBSourceNameType = NULL
, @Parm41 WBSourceNameType = NULL
, @Parm42 WBSourceNameType = NULL
, @Parm43 WBSourceNameType = NULL
, @Parm44 WBSourceNameType = NULL
, @Parm45 WBSourceNameType = NULL
, @Parm46 WBSourceNameType = NULL
, @Parm47 WBSourceNameType = NULL
, @Parm48 WBSourceNameType = NULL
, @Parm49 WBSourceNameType = NULL
, @Parm50 WBSourceNameType = NULL
) AS编写计算值，将它指定给 @Amount 的逻辑。将返回显示给此用户的数额。
为检索您可能已经设置的任何参数，您可调用一个标准函数。它是 dbo.SSSWBGetParm。传递您在在处理的关键编号 (@KPINum)，以及您要检索的参数。此参数先在此特定的关键编号中查找它，然后在“关键编号参数”表单的基本列表中查找。要检索名为“Acct”的参数，在您的存储过程中将它设为变量，执行以下操作：
过期订单行示例
CREATE PROCEDURE SSSWBCanCoitemPastDueSp (
@KPINum WBKPINumType
, @AsOfDate DateType
, @Amount AmountType OUTPUT
, @Parm1 WBSourceNameType = NULL
, @Parm2 WBSourceNameType = NULL
, @Parm3 WBSourceNameType = NULL
, @Parm4 WBSourceNameType = NULL
, @Parm5 WBSourceNameType = NULL
, @Parm6 WBSourceNameType = NULL
, @Parm7 WBSourceNameType = NULL
, @Parm8 WBSourceNameType = NULL
, @Parm9 WBSourceNameType = NULL
, @Parm10 WBSourceNameType = NULL
, @Parm11 WBSourceNameType = NULL
, @Parm12 WBSourceNameType = NULL
, @Parm13 WBSourceNameType = NULL
, @Parm14 WBSourceNameType = NULL
, @Parm15 WBSourceNameType = NULL
, @Parm16 WBSourceNameType = NULL
, @Parm17 WBSourceNameType = NULL
, @Parm18 WBSourceNameType = NULL
, @Parm19 WBSourceNameType = NULL
, @Parm20 WBSourceNameType = NULL
, @Parm21 WBSourceNameType = NULL
, @Parm22 WBSourceNameType = NULL
, @Parm23 WBSourceNameType = NULL
, @Parm24 WBSourceNameType = NULL
, @Parm25 WBSourceNameType = NULL
, @Parm26 WBSourceNameType = NULL
, @Parm27 WBSourceNameType = NULL
, @Parm28 WBSourceNameType = NULL
, @Parm29 WBSourceNameType = NULL
, @Parm30 WBSourceNameType = NULL
, @Parm31 WBSourceNameType = NULL
, @Parm32 WBSourceNameType = NULL
, @Parm33 WBSourceNameType = NULL
, @Parm34 WBSourceNameType = NULL
, @Parm35 WBSourceNameType = NULL
, @Parm36 WBSourceNameType = NULL
, @Parm37 WBSourceNameType = NULL
, @Parm38 WBSourceNameType = NULL
, @Parm39 WBSourceNameType = NULL
, @Parm40 WBSourceNameType = NULL
, @Parm41 WBSourceNameType = NULL
, @Parm42 WBSourceNameType = NULL
, @Parm43 WBSourceNameType = NULL
, @Parm44 WBSourceNameType = NULL
, @Parm45 WBSourceNameType = NULL
, @Parm46 WBSourceNameType = NULL
, @Parm47 WBSourceNameType = NULL
, @Parm48 WBSourceNameType = NULL
, @Parm49 WBSourceNameType = NULL
, @Parm50 WBSourceNameType = NULL
) AS
DECLARE @CoStatList LongListType
, @CoitemStatList LongListType
, @CredHold ListYesNoType
, @LateDays GenericIntType
, @QtyDue QtyUnitType
, @OrdTotal AmountType
, @ParmsSite SiteType
, @CustNum CustNumType
, @CoNum CoNumType
, @LineFilter CoLineType
, @ItemFilter ItemType
, @ProdCodeFilter ProductCodeType
, @WhseFilter WhseType
, @StatFilter CoitemStatusType
SELECT @ParmsSite = site
FROM parms
SET @CoStatList = ISNULL(dbo.SSSWBGetParm(@CrNum, 'COStatusList'), 'POS')
SET @CoitemStatList = ISNULL(dbo.SSSWBGetParm(@CrNum, 'COITEMStatusList'), 'PO')
SET @CredHold = ISNULL(dbo.SSSWBGetParm(@CrNum, 'CredHold'), 0)
SET @LateDays = ISNULL(dbo.SSSWBGetParm(@CrNum, 'LateDaysTolerance'), 0)
SET @CustNum = dbo.ExpandKyByType('CustNumType', @Parm1)
SET @CoNum = dbo.ExpandKyByType('CoNumType', @Parm2)
SET @LineFilter = NULLIF(@Parm3, '')
SET @ItemFilter = NULLIF(@Parm4, '')
SET @ProdCodeFilter = NULLIF(@Parm5, '')
SET @WhseFilter = NULLIF(@Parm6, '')
SET @StatFilter = NULLIF(@Parm7, '')
SELECT @Amount = COUNT(*)
FROM coitem
LEFT OUTER JOIN item itm
ON itm.item = coitem.item
WHERE (@CoNum IS NULL OR co_num = @CoNum)
AND charindex(coitem.stat, @CoitemStatList) > 0
AND qty_ordered > qty_shipped
AND ISNULL(due_date, '1900-01-01') >= dateadd(dd, @LateDays, @AsOfDate)
AND ship_site = @ParmsSite
AND EXISTS (SELECT 1 FROM co
WHERE (@CustNum IS NULL OR co.cust_num = @CustNum)
AND co.co_num = coitem.co_num
AND charindex(co.stat, @CoStatList) > 0
AND co.credit_hold = @CredHold
)
AND (@LineFilter IS NULL OR coitem.co_line = @LineFilter)
AND (@ItemFilter IS NULL OR coitem.item = @ItemFilter)
AND (@ProdCodeFilter IS NULL OR itm.product_code = @ProdCodeFilter)
AND EXISTS (SELECT 1 FROM co
WHERE (@WhseFilter IS NULL OR co.whse = @WhseFilter)
AND (@StatFilter IS NULL OR co.stat = @StatFilter)
AND co.co_num = coitem.co_num)
RETURN 0相关主题