我需要实现的是将未知数量的值的列表发送到Sql服务器的NOT IN子句,但只能使用奇异的值来实现。以下是我的Sql语句:
SELECT SorMaster.LastInvoice
, SorMaster.SalesOrder
, SorMaster.OrderStatus
, ArCustomer.RouteCode
, SorMaster.Customer
, SorMaster.CustomerName
, SorMaster.CustomerPoNumber
, SorMaster.OrderDate
, SorMaster.DateLastInvPrt
, ArInvoice.InvoiceBal1
, ArInvoice.TermsCode
FROM SorMaster AS SorMaster
INNER JOIN ArCustomer AS ArCustomer ON SorMaster.Customer = ArCustomer.Customer
INNER JOIN ArInvoice AS ArInvoice ON SorMaster.LastInvoice = ArInvoice.Invoice
WHERE (SorMaster.OrderStatus = '9')
AND (SorMaster.Branch LIKE 'J%')
AND (SorMaster.DocumentType = 'O')
AND (SorMaster.LastInvoice > @Last_Invoice)
AND (SorMaster.OrderDate > DATEADD(Month, - 4, GETDATE()))
AND (SorMaster.LastInvoice NOT IN (@ExclusionList))
ORDER BY SorMaster.LastInvoice
此代码将@ExclusionList值作为字符串从列表框中生成:
Dim exclusion As String = ""
If MenuForm.ExclusionCB.Checked = True Then
For i = 0 To MenuForm.ExclusionLB.Items.Count - 2
exclusion = exclusion & MenuForm.ExclusionLB.Items(i) & ","
Next
exclusion = exclusion & MenuForm.ExclusionLB.Items(MenuForm.ExclusionLB.Items.Count - 1)
Else
exclusion = ""
End If
我也尝试过将整个列表框作为集合发送。
有谁知道我可以发送多个值(诸如1,2,3,4,5,6之类的值),并且让sql理解这些值不止一个?只要SELECT语句返回相同的信息,我就不会有任何问题。
我需要例外列表的原因是,我们的远程数据库PK在Salesorder列上,而本地数据库在LastInvoice列上
希望这是有道理的。如果您需要更多信息,请告诉我
您可以将其作为字符串发送并使用动态sql。这是一个简单的示例。
DECLARE @vals VARCHAR(50) = '1,2,3,4,5,6'
DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM TABLE WHERE FIELD1 IN'
SET @sql = @sql + ' (' + @vals + ')'
-- @sql = 'SELECT * FROM TABLE WHERE FIELD1 IN (1,2,3,4,5,6)'
EXEC (@sql)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句