SQL Case语句故障
SQL Case语句故障
提问于 2012-03-23 16:26:51
当涉及到SQL时，我并不是最好的，还在学习诀窍。我的SQL服务器管理器2008中有一个存储过程。
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[GetSalesBuyers] Script Date: 03/23/2012 08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers]
@Cdisc varchar(255),
@bcs varchar(255),
@From date,
@Too date
AS
SELECT i.Acct,
i.Name,
i.Document,
i.Part,
i.Qty,
i.Unit,
dbo.NEWPareto.Pareto,
i.pg,
dbo.MyPgTable.PgName,
i.[DateTime],
i.BinSeqNo,
i.cdisc,
i.bcs
FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
dbo.iHeads.name,
dbo.iLines.Document,
dbo.iLines.Part,
dbo.iLines.Pg,
dbo.iLines.Qty,
dbo.iLines.unit,
dbo.iHeads.[DateTime],
dbo.iLines.BinSeqNo,
dbo.Customer.cdisc,
dbo.Customer.Bcs
FROM Autopart.dbo.iheads INNER JOIN Autopart.dbo.iLines ON
Autopart.dbo.Iheads.document = autopart.dbo.iLines.document
INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct
= Autopart.dbo.customer.keycode
GROUP By dbo.iHeads.acct,
dbo.iHeads.name,
dbo.iLines.Document,
dbo.iLines.Part,
dbo.iLines.Pg,
dbo.iLines.Qty,
dbo.iLines.unit,
dbo.iHeads.[DateTime],
dbo.iLines.BinSeqNo,
dbo.Customer.cdisc,
dbo.Customer.bcs
') i
left JOIN
dbo.NEWPareto
ON
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
left JOIN
dbo.MyPgTable
ON
i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]
WHERE
(i.[DateTime] BETWEEN @From AND @Too)
AND i.cdisc = @Cdisc
AND i.bcs != @bcs
AND i.pg != '60'
AND i.pg != '61'
AND i.pg != '62'
GROUP BY i.Acct,
i.Name,
i.Document,
i.Part,
i.Qty,
i.Unit,
dbo.NEWPareto.Pareto,
i.pg,
dbo.MyPgTable.PgName,
i.[DateTime],
i.BinSeqNo,
i.cdisc,
i.bcs我需要的是Where子句中的一个条件。特别是这一行“和i.bcs != @bcs”。
发生的情况是，如果我向参数传递一个空字符串，我希望该行存在并在where子句中运行。
否则，如果我没有传递任何东西(null)，我需要行不存在(不是run)。
我一直在玩，但当我尝试创作时，我得到的只是到处都是红线。
有谁有主意吗？我的方法接近了吗？这是可以做到的吗？或者有没有一种简单的模式方式可以让我忽略。非常感谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-03-23 16:41:52
可选参数的结构如下：
WHERE (@bcs is null OR i.bcs != @bcs)如果发送null，则选择每一行；如果发送任何其他内容，则只选择不匹配的行。您不应该担心性能，因为Sql Server非常擅长使用常量表达式，如果它的计算结果为1，它将被取消。
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[GetSalesBuyers] Script Date: 03/23/2012 08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers]
@Cdisc varchar(255),
@bcs varchar(255),
@From date,
@Too date
AS
SELECT i.Acct,
i.Name,
i.Document,
i.Part,
i.Qty,
i.Unit,
dbo.NEWPareto.Pareto,
i.pg,
dbo.MyPgTable.PgName,
i.[DateTime],
i.BinSeqNo,
i.cdisc,
i.bcs
FROM
OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
dbo.iHeads.name,
dbo.iLines.Document,
dbo.iLines.Part,
dbo.iLines.Pg,
dbo.iLines.Qty,
dbo.iLines.unit,
dbo.iHeads.[DateTime],
dbo.iLines.BinSeqNo,
dbo.Customer.cdisc,
dbo.Customer.Bcs
FROM Autopart.dbo.iheads INNER JOIN Autopart.dbo.iLines ON
Autopart.dbo.Iheads.document = autopart.dbo.iLines.document
INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct
= Autopart.dbo.customer.keycode
GROUP By dbo.iHeads.acct,
dbo.iHeads.name,
dbo.iLines.Document,
dbo.iLines.Part,
dbo.iLines.Pg,
dbo.iLines.Qty,
dbo.iLines.unit,
dbo.iHeads.[DateTime],
dbo.iLines.BinSeqNo,
dbo.Customer.cdisc,
dbo.Customer.bcs
') i
left JOIN
dbo.NEWPareto
ON
i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part
left JOIN
dbo.MyPgTable
ON
i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]
WHERE
(i.[DateTime] BETWEEN @From AND @Too)
AND i.cdisc = @Cdisc
AND (@bcs is null OR i.bcs != @bcs)
AND i.pg != '60'
AND i.pg != '61'
AND i.pg != '62'
GROUP BY i.Acct,
i.Name,
i.Document,
i.Part,
i.Qty,
i.Unit,
dbo.NEWPareto.Pareto,
i.pg,
dbo.MyPgTable.PgName,
i.[DateTime],
i.BinSeqNo,
i.cdisc,
i.bcsStack Overflow用户
发布于 2012-03-23 16:32:30
据我所知，你能做到这一点的唯一方法(你想要的就是使用动态sql)，也就是说，它并不美观。您将整个proc放在一个string变量中，然后根据需要构建它。然后最后执行它。稍后我会用一个样例更新这篇文章。
看看这个link，它的语法看起来是怎样的。
不要对我评头论足，我通常不会像这样写代码
但这就是你要找的部分
IF (ISNULL(@bcs,0) <> 0)
SET @SQL = @SQL + ' AND i.bcs != '+@bcs + CHAR(13)您已更新的进程。
USE [ShaftData]
GO
/****** Object: StoredProcedure [dbo].[GetSalesBuyers] Script Date: 03/23/2012 08:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSalesBuyers]
@Cdisc varchar(255),
@bcs varchar(255),
@From date,
@Too date
AS
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ' SELECT i.Acct, i.Name, i.Document, i.Part, i.Qty, i.Unit, dbo.NEWPareto.Pareto, i.pg,' + CHAR(13)
SET @SQL = @SQL + ' dbo.MyPgTable.PgName,i.[DateTime],i.BinSeqNo,i.cdisc,i.bcs' + CHAR(13)
SET @SQL = @SQL + ' FROM ' + CHAR(13)
SET @SQL = @SQL + ' OPENQUERY(SACBAUTO, ''SELECT dbo.iHeads.acct, dbo.iHeads.name,dbo.iLines.Document,dbo.iLines.Part, ' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.Pg,dbo.iLines.Qty,dbo.iLines.unit,dbo.iHeads.[DateTime], dbo.iLines.BinSeqNo, ' + CHAR(13)
SET @SQL = @SQL + ' dbo.Customer.cdisc,dbo.Customer.Bcs ' + CHAR(13)
SET @SQL = @SQL + ' FROM Autopart.dbo.iheads INNER JOIN Autopart.dbo.iLines ON ' + CHAR(13)
SET @SQL = @SQL + ' Autopart.dbo.Iheads.document = autopart.dbo.iLines.document' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN Autopart.dbo.Customer ON Autopart.dbo.iheads.acct ' + CHAR(13)
SET @SQL = @SQL + ' = Autopart.dbo.customer.keycode' + CHAR(13)
SET @SQL = @SQL + ' GROUP By dbo.iHeads.acct,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iHeads.name,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.Document,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.Part,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.Pg,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.Qty,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.unit,' + CHAR(13)
SET @SQL = @SQL + ' dbo.iHeads.[DateTime],' + CHAR(13)
SET @SQL = @SQL + ' dbo.iLines.BinSeqNo,' + CHAR(13)
SET @SQL = @SQL + ' dbo.Customer.cdisc,' + CHAR(13)
SET @SQL = @SQL + ' dbo.Customer.bcs' + CHAR(13)
SET @SQL = @SQL + ' '') i' + CHAR(13)
SET @SQL = @SQL + 'left JOIN' + CHAR(13)
SET @SQL = @SQL + 'dbo.NEWPareto' + CHAR(13)
SET @SQL = @SQL + 'ON ' + CHAR(13)
SET @SQL = @SQL + 'i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part ' + CHAR(13)
SET @SQL = @SQL + 'left JOIN' + CHAR(13)
SET @SQL = @SQL + 'dbo.MyPgTable ' + CHAR(13)
SET @SQL = @SQL + 'ON ' + CHAR(13)
SET @SQL = @SQL + ' i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]' + CHAR(13)
SET @SQL = @SQL + 'WHERE' + CHAR(13)
SET @SQL = @SQL + ' (i.[DateTime] BETWEEN ''+@From+'' AND ''+@Too+'') ' + CHAR(13)
SET @SQL = @SQL + ' AND i.cdisc = '+ @Cdisc + CHAR(13)
IF (ISNULL(@bcs,0) <> 0)
SET @SQL = @SQL + ' AND i.bcs != '+@bcs + CHAR(13)
SET @SQL = @SQL + ' AND i.pg != ''60''' + CHAR(13)
SET @SQL = @SQL + ' AND i.pg != ''61''' + CHAR(13)
SET @SQL = @SQL + ' AND i.pg != ''62''' + CHAR(13)
SET @SQL = @SQL + ' GROUP BY i.Acct,' + CHAR(13)
SET @SQL = @SQL + ' i.Name, ' + CHAR(13)
SET @SQL = @SQL + ' i.Document, ' + CHAR(13)
SET @SQL = @SQL + ' i.Part, ' + CHAR(13)
SET @SQL = @SQL + ' i.Qty, ' + CHAR(13)
SET @SQL = @SQL + ' i.Unit, ' + CHAR(13)
SET @SQL = @SQL + ' dbo.NEWPareto.Pareto, ' + CHAR(13)
SET @SQL = @SQL + ' i.pg,' + CHAR(13)
SET @SQL = @SQL + ' dbo.MyPgTable.PgName, ' + CHAR(13)
SET @SQL = @SQL + ' i.[DateTime],' + CHAR(13)
SET @SQL = @SQL + ' i.BinSeqNo,' + CHAR(13)
SET @SQL = @SQL + ' i.cdisc,' + CHAR(13)
SET @SQL = @SQL + ' i.bcs' + CHAR(13)EXEC(@SQL)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9836009
复制相关文章
相似问题
腾讯云开发者
