修改存储过程

[ttg520]

string selectSql = "select id,orderNO, orderdate, custCode, buyer, contact, exchName, taxRate, purchaseName,payPeriod, packType, shipAddress, toucher,createdate,remark from V_TM_PreSalesOrder where auditFalg=0 and orderdate between '" + fromDate + "' and '" + toDate + "'";
StringBuilder selectSqlBuilder = new StringBuilder();
selectSqlBuilder.Append(selectSql);
if (custList != null)
{
selectSqlBuilder.Append(" and custCode in (" + custList + ")");
}
if (orderNO != null)
{
selectSqlBuilder.Append(" and orderNo like '%" + orderNO + "%'");
}

这是以前写的查询语句是根据条件 拼成一句Sql语句 请问在存储过程里怎么写啊

[ttg520]

自己顶

[paoluo]

--如果custCode是數值類型的。


--創建存儲過程
Create ProceDure SP_TEST(@fromDate DateTime, @toDate DateTime, @custList Varchar(1000), @orderNO Varchar(100))
As
Begin
Declare @selectSql Varchar(8000)
Select @selectSql = ' select id,orderNO, orderdate, custCode, buyer, contact, exchName, taxRate, purchaseName,payPeriod, packType, shipAddress, toucher,createdate,remark from V_TM_PreSalesOrder '
Select @selectSql= @selectSql + ' where auditFalg=0 '
Select @selectSql= @selectSql + ' and orderdate between ''' + Convert(Varchar(10), @fromDate, 120) + ''' And ''' + Convert(Varchar(10), @toDate, 120) + ''''
If(IsNull(@custList, '') != '')
Select @selectSql= @selectSql + ' and custCode in (' + @custList + ')'
If(IsNull(@orderNO, '') != '')
Select @selectSql= @selectSql + ' and orderNo like ''%' + @orderNO + '%'''
EXEC(@selectSql)
End
--調用存儲過程
EXEC SP_TEST '2007-01-01', '2007-07-21', '1,3,5', 'AA'

[ttg520]

感谢 paoluo(一天到晚游泳的鱼)