实在不知道该如何下手,我是用ASP设计程序,其中ProductList是数组: sql = "Select cpid,cpbm,class1,cpmc,sjg,yhj,gb,pp From cpb" sql = sql & " Where cpbm In (" & ProductList & ")" sql = sql & " Order By cpid" 现在该如何写这一存储过程?
USE NorthWindcreate table #tmp(ID int)insert #tmp values(10248) insert #tmp values(10249) insert #tmp values(10250) insert #tmp values(10251)select * from #tmp goCREATE PROCEDURE asp_cpbselect AS --select all data SELECT OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight FROM Orders Where OrderID IN (SELECT * FROM #tmp) Order By OrderIDGOEXEC asp_cpbselect
CREATE PROCEDURE asp_cpbselect @ProductList varchar(8000) AS exec('SELECT cpid,cpbm,class1,cpmc,sjg,yhj,gb,pp FROM cpb where Where cpbm IN ('+@ ProductList+') Order By cpid')GO--调用 exec asp_cpbselect '''aa'',''bb'',''cc''' --或: exec asp_cpbselect '1,2,3,5'
2,cccc
3,ccc
4,dddd
sql = "Select cpid,cpbm,class1,cpmc,sjg,yhj,gb,pp From cpb"
sql = sql & " Where cpbm In (" & ProductList & ")"
sql = sql & " Order By cpid" 现在该如何写这一存储过程?
insert #tmp values(10249)
insert #tmp values(10250)
insert #tmp values(10251)select * from #tmp
goCREATE PROCEDURE asp_cpbselect
AS --select all data
SELECT OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight
FROM Orders Where OrderID IN (SELECT * FROM #tmp) Order By OrderIDGOEXEC asp_cpbselect
@ProductList varchar(8000)
AS exec('SELECT cpid,cpbm,class1,cpmc,sjg,yhj,gb,pp FROM cpb where Where cpbm IN ('+@ ProductList+') Order By cpid')GO--调用
exec asp_cpbselect '''aa'',''bb'',''cc'''
--或:
exec asp_cpbselect '1,2,3,5'
=================================
将@productlist以一个字符串的形式传入存储过程,然后进行字符串分割,插入到临时表,然后实现select 查询
=================================
我觉得能这样做是最好的,怎么实现呢?