--region [dbo].[GSTA_GetCreditCartListAmount]
------------------------------------------------------------------------------------------------------------------------
-- Generated By: Jam.Fu
-- Mark: 根据條件查詢總數量與總訂單價
-- Procedure Name: [dbo].[GSTA_GetCreditCartListAmount]
-- Date Generated: 2008年4月1日
------------------------------------------------------------------------------------------------------------------------
Create PROC dbo.GSTA_GetCreditCartListAmount
@StrWhere varchar(500)
AS
DECLARE @Sql nvarchar(4000)
SET @Sql='
SELECT
Count(*),
SUM(OM.Amount)
FROM
OrderM OM
INNER JOIN
OrderMkt OMkt
ON
OM.OrderNo = OMkt.OrderNo
Left JOIN
GemProduct.dbo.Market mkt
ON
mkt.MktID = OMkt.MktID
Left JOIN
GemSales.dbo.QueryMarket qmkt
ON
OMkt.MktID = qmkt.QueryMarketID
LEFT JOIN
GemProduct.dbo.MktPaytypeInfo pay
ON
pay.Paytype = OM.PayType
WHERE '+@StrWhere
EXEC sp_executesql @SQL
--endregion
exec GSTA_FuDaSCreditCartList @StrWhere = ' OM.OrderTime>''2008-04-02'' and OM.OrderTime<''08/04/10'' and OM.StatusNo =300 and OM.PayKinds=7 and OM.OrderNo Like '' %%'' or OM.OName Like ''%%'' or OM.OrderID Like ''%%'' or OM.SendProTypeNo Like ''%%'' or OM.RName Like ''%%'' and OM.BankKind =0 and OM.PayType=3 and OM.CTID=''939e7770-169b-4751-b460-c706e4b1ebd4'''
--结果里面把paykinds为null值的全取出来了。。
------------------------------------------------------------------------------------------------------------------------
-- Generated By: Jam.Fu
-- Mark: 根据條件查詢總數量與總訂單價
-- Procedure Name: [dbo].[GSTA_GetCreditCartListAmount]
-- Date Generated: 2008年4月1日
------------------------------------------------------------------------------------------------------------------------
Create PROC dbo.GSTA_GetCreditCartListAmount
@StrWhere varchar(500)
AS
DECLARE @Sql nvarchar(4000)
SET @Sql='
SELECT
Count(*),
SUM(OM.Amount)
FROM
OrderM OM
INNER JOIN
OrderMkt OMkt
ON
OM.OrderNo = OMkt.OrderNo
Left JOIN
GemProduct.dbo.Market mkt
ON
mkt.MktID = OMkt.MktID
Left JOIN
GemSales.dbo.QueryMarket qmkt
ON
OMkt.MktID = qmkt.QueryMarketID
LEFT JOIN
GemProduct.dbo.MktPaytypeInfo pay
ON
pay.Paytype = OM.PayType
WHERE '+@StrWhere
EXEC sp_executesql @SQL
--endregion
exec GSTA_FuDaSCreditCartList @StrWhere = ' OM.OrderTime>''2008-04-02'' and OM.OrderTime<''08/04/10'' and OM.StatusNo =300 and OM.PayKinds=7 and OM.OrderNo Like '' %%'' or OM.OName Like ''%%'' or OM.OrderID Like ''%%'' or OM.SendProTypeNo Like ''%%'' or OM.RName Like ''%%'' and OM.BankKind =0 and OM.PayType=3 and OM.CTID=''939e7770-169b-4751-b460-c706e4b1ebd4'''
--结果里面把paykinds为null值的全取出来了。。
解决方案 »
- SQL 断言问题
- 谁有 购物网站的 后台设计?
- ~~~~~~~~!~~~~~~~~数据库备份除了生成insert数据和bak文件,还有哪些备份的方法人们用到的,谢谢指导!!~~~~~~~~~~~~~~~~~~~~~
- 今天决定来SQL区散分. 实在是太郁闷了......数据库没有完全关闭,无法重新生成日志
- sqlserver中一个登录名只能建一个用户名吗?
- LEFT JOIN 比 INNER JOIN快吗?
- 查录30-40条记录,根据ID不连续的排序查询 有一个新闻表和目录表,查询每个目录下最新的一条新闻
- 求SQL写法?(如何删除A表中和B表相同的记录)(在线)
- 请教问题 :(错误137)
- 关于存储过程与函数
- 请帮我看看sql语句的错误
- 简单问题,字段外联
or
sum(isnull(om.amount,0))
FROM pubs.dbo.titles
WHERE type = 'business' OR type = 'psychology'
AND advance > $5500可以通过添加括号强制首先对 OR 取值的办法来改变查询的含义。以下查询查找所有价格超过 $5,500 的商业书籍和心理书籍:SELECT title_id, type, advance
FROM titles
WHERE (type = 'business' OR type = 'psychology')
AND advance > $5500由于存在运算符优先顺序,使用括号(即使不要求)可以提高查询的可读性,并能减少微小错误的发生。使用括号不会造成重大的性能损失。尽管在语法构成上是相同的,但是下例比原例具有更强的可读性:SELECT title_id, type, advance
FROM pubs.dbo.titles
WHERE type = 'business'
OR (type = 'psychology' AND advance > $5500)
--region [dbo].[GSTA_FuDaSCreditCartList]
------------------------------------------------------------------------------------------------------------------------
-- Generated By: Jam.Fu
-- Mark: 根据條件查詢信用錶資訊
-- Procedure Name: [dbo].[GSTA_FuDaSCreditCartList]
-- Date Generated: 2008年4月1日
------------------------------------------------------------------------------------------------------------------------
CREATE PROC dbo.GSTA_FuDaSCreditCartList
@StrWhere varchar(500)
AS
DECLARE @Sql nvarchar(4000)
SET @Sql='
SELECT DISTINCT
OM.SupplyDate,
OM.OrderNo AS OrderNo,
OM.OName,
OM.TrsCode,
OM.RName,
OM.PayNo,
pay.Detail PayType,
CONVERT(VARCHAR(16),OM.CrTime,20) OrderTime,
OM.PayAmount Amount,
OM.Amount OAmount,
(SELECT mi.Name FROM OrderStatusDef mi WHERE mi.StatusNo = OM.StatusNo) AS OrderStatus,
CASE WHEN CreditCardSupplyTime IS NULL THEN
''-''
ELSE
CONVERT(VARCHAR(16),CreditCardSupplyTime,20)
END AS CreditCardSupplyTime,
case when OM.PayKinds=1 then ''未繳款(ATM)''
when OM.PayKinds=2 then ''已入帳(ATM)''
when OM.PayKinds=3 then ''待退款(ATM)''
when OM.PayKinds=4 then ''退款完成(ATM)''
when OM.PayKinds=5 then ''已授權(信用卡)''
when OM.PayKinds=6 then ''取消授權(信用卡)''
when OM.PayKinds=7 then ''已請款(信用卡)''
when OM.PayKinds=8 then ''取消請款(信用卡)''
end as PayKinds,
case when OM.CreditCardSupplyType=1 then ''申請請款''
when OM.CreditCardSupplyType=2 then ''取消授權''
when OM.CreditCardSupplyType=3 then ''取消請款''
when OM.CreditCardSupplyType=4 then ''取消交易''
end as CreditCardSupplyType,
CASE WHEN(OM.WithInvoice = 1)
THEN
case when InvoSendTime is null then
''''
else
''寄送''
end
ELSE
''寄送''
END AS WithInvoice
FROM
OrderM OM
INNER JOIN
OrderMkt OMkt
ON
OM.OrderNo = OMkt.OrderNo
Left JOIN
GemProduct.dbo.Market mkt
ON
mkt.MktID = OMkt.MktID
Left JOIN
GemSales.dbo.QueryMarket qmkt
ON
OMkt.MktID = qmkt.QueryMarketID
LEFT JOIN
GemProduct.dbo.MktPaytypeInfo pay
ON
pay.Paytype = OM.PayType
WHERE '+@StrWhere+' ORDER BY
OM.SupplyDate DESC
'
EXEC sp_executesql @SQL
--endregion
left JOIN
OrderMkt OMkt
這是事件探查器中運行的語句。
exec GSTA_FuDaSCreditCartList @StrWhere = ' OM.OrderTime>'''' and OM.StatusNo =310 and OM.PayKinds=6 and OM.PayKinds is not null and OM.OrderNo Like '' %%'' or OM.OName Like ''%%'' or OM.OrderID Like ''%%'' or OM.SendProTypeNo Like ''%%'' or OM.RName Like ''%%'' and OM.BankKind =1 and OM.PayType=3 and OM.CTID=''939e7770-169b-4751-b460-c706e4b1ebd4'''
from OrderM
PayKinds is null有记录吗?
from OrderM
PayKinds is not null
2000中语法错误!
四楼的是对的哦。
还有我的条件语句也拼错了点!