SQL程序代码在Query Analyzer下运行没问题:
select * from shipment
where Pallet in ('APR480001','APR480006','APR480004','APR480008','APR480009') and PalletStatus=1
Order by Pallet
现在要将where Pallet in ('APR480001','APR480006','APR480004','APR480008','APR480009') 括号内的条件用参数 :pallet 传递过来,SQL查询果却为空。以下是ADOQUERY1的SQL属性:SELECT *
FROM dbo.Shipment
WHERE (Pallet IN (:pallet)) AND (PalletStatus = 1)
ORDER BY Pallet, SerialNumber
请大侠指点 where 查询条件中用 IN 参数该怎么传递?
select * from shipment
where Pallet in ('APR480001','APR480006','APR480004','APR480008','APR480009') and PalletStatus=1
Order by Pallet
现在要将where Pallet in ('APR480001','APR480006','APR480004','APR480008','APR480009') 括号内的条件用参数 :pallet 传递过来,SQL查询果却为空。以下是ADOQUERY1的SQL属性:SELECT *
FROM dbo.Shipment
WHERE (Pallet IN (:pallet)) AND (PalletStatus = 1)
ORDER BY Pallet, SerialNumber
请大侠指点 where 查询条件中用 IN 参数该怎么传递?
'APR480001,APR480006,APR480004,APR480008,APR480009'當然就得到結果了。
正確的做法應該這樣:
Str := ' 'APR480001','APR480006','APR480004','APR480008','APR480009' ';sqlStr := 'select * from shipment '+
'where '+
Format('Pallet in (%s) and PalletStatus=1 ',[Str]) +
'Order by Pallet ';
注:一定要將字符串處理成上面Str的格式。
Str := ' 'APR480001','APR480006','APR480004','APR480008','APR480009' ';我個人覺得能不這樣做還是換一個辦法好
如果你一定要這樣用也不是不行,方法跟正常的參數一樣
隻是你要注意一下傳進去的東西保持原樣~~~所以在目標字符串中如果有一個('),
那在原串中得有兩個一起 。
Assignfile(w,'c:\pallet.txt');
reset(w);
readln(w,s);
pallet := s;
ClosefILE(W);
// Memo1.text :=s;
ADOQuery5.Close; Str := ' ' + pallet + ' '; sqlStr := 'select NO=Identity(int,1,1),SerialNumber,Pallet,InputTime into #query5 from shipment '+
'where '+
Format('Pallet in (%s) and PalletStatus=1 ',[Str]) +
'Order by Pallet' +
' select * from #query5 ' +
'DROP TABLE #query5';
ADOQuery5.SQL.Add(sqlstr);