------------------------------------------------------------------------------------------------------------------------ -- 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 varchar(8000) SET @Sql=' SELECT DISTINCT OM.OrderNo AS OrderNo, OM.OName, OM.TrsCode, OM.RName, OM.PayNo, pay.Detail PayType, CONVERT(VARCHAR(16),OM.CrTime,20) OrderTime, PayAmount Amount, (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 PayKinds=1 then ''未繳款(ATM)'' when PayKinds=2 then ''已入帳(ATM)'' when PayKinds=3 then ''待退款(ATM)'' when PayKinds=4 then ''退款完成(ATM)'' when PayKinds=5 then ''已授權(信用卡)'' when PayKinds=6 then ''取消授權(信用卡)'' when PayKinds=7 then ''已請款(信用卡)'' when PayKinds=8 then ''取消請款(信用卡)'' end as PayKinds, 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 Go @StrWhere 这可以就在存储过程中拼出来的!~
可多表关联没有一丁点关系。 拼接sql字符串就可以了。这个不是,sql 中办到的,而是语言中拼接的。string sql="select * from tb where 1=1 "; if (textboxa.text.trim() != "") { sql = sql +" and a = '" + textboxa.text + "' "; } .... 与此类推下去,也可以不用判断直接把所有条件都加到后面,只是条件表达式用like,也可以的。
把基本的表连接条件写好然后where 后用一个恒等式即可。例如 '1=1'
就是这样了 先在page_load中 String strLawyer = page.request.QueryString(''); ...... 再在button_click中 string sql="select * from tb where 1=1 "; if (strLawyer != "") { sql = sql +" and lawyer = '" + strLawyer + "' "; } if (strProvince != "") { sql = sql +" and province = '" + strProvince + "' "; } if (strCity != "") { sql = sql +" and city = '" + strCity + "' "; } if (strZy!= "") { sql = sql +" and zy = '" + strZy + "' "; }
select [User].Id,Photo,RealityName from LawyerUser join [User] on LawyerUser.UserId=[User].ID where 1=1 and RealityName = ''and ProvinceId='8'and CityId='0'and LawyerSpecialityId='0'就这样,不行,查不到数据。
那把这句放在SQL里运行有数据?
create procedure P_Test (@TJ nvarchar(100) =null) as begin if @TJ is not null begin select * from tb where @TJ ... end else begin select * from tb end end
-- 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 varchar(8000)
SET @Sql='
SELECT DISTINCT
OM.OrderNo AS OrderNo,
OM.OName,
OM.TrsCode,
OM.RName,
OM.PayNo,
pay.Detail PayType,
CONVERT(VARCHAR(16),OM.CrTime,20) OrderTime,
PayAmount Amount,
(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 PayKinds=1 then ''未繳款(ATM)''
when PayKinds=2 then ''已入帳(ATM)''
when PayKinds=3 then ''待退款(ATM)''
when PayKinds=4 then ''退款完成(ATM)''
when PayKinds=5 then ''已授權(信用卡)''
when PayKinds=6 then ''取消授權(信用卡)''
when PayKinds=7 then ''已請款(信用卡)''
when PayKinds=8 then ''取消請款(信用卡)''
end as PayKinds,
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
Go
@StrWhere 这可以就在存储过程中拼出来的!~
拼接sql字符串就可以了。这个不是,sql 中办到的,而是语言中拼接的。string sql="select * from tb where 1=1 ";
if (textboxa.text.trim() != "")
{
sql = sql +" and a = '" + textboxa.text + "' ";
}
....
与此类推下去,也可以不用判断直接把所有条件都加到后面,只是条件表达式用like,也可以的。
就是这样了
先在page_load中
String strLawyer = page.request.QueryString('');
......
再在button_click中
string sql="select * from tb where 1=1 ";
if (strLawyer != "")
{
sql = sql +" and lawyer = '" + strLawyer + "' ";
}
if (strProvince != "")
{
sql = sql +" and province = '" + strProvince + "' ";
}
if (strCity != "")
{
sql = sql +" and city = '" + strCity + "' ";
}
if (strZy!= "")
{
sql = sql +" and zy = '" + strZy + "' ";
}
然后分别判断每个选择条件是否有条件再给查询语句添加条件其中 “where 0=0 ” 是为了方便下面的查询语句不需要判断直接可以加上 “and”
来 显示 相应的信息,
后一个页面我是用DataList 绑定的
create procedure P_Test (@TJ nvarchar(100) =null)
as
begin
if @TJ is not null
begin
select * from tb where @TJ
...
end
else
begin
select * from tb
end
end