以下是在ASP中的程序,我想使用存储过程,不知如何下手了……===========
'列:x0 x1 x2 x3 x4 x5 x6 x7 x8 x9 jieguo
'request.form y0……y9 10个选择框参数,不选则为0,0表示全部,其中y0是必选项。
程序如下:……
dim s1,s2,s3……
y0=request.form("y0")
y1=request.form("y1")
if y1="0" then
s1=""
else
s1=" and x1='"&y1&"'"
end if
……
y9=request.form("y9")
if y9="0" then
s9=""
else
s9=" and x9='"&y9&"'"
end ifmyabc="x0='"&y0&"'"&s1&s2&s3&……&s9set rs=server.createobject("ADODB.recordset")
sq="select jieguo from abc where "&myabc
rs.open sq,conn,1,3
……
'列:x0 x1 x2 x3 x4 x5 x6 x7 x8 x9 jieguo
'request.form y0……y9 10个选择框参数,不选则为0,0表示全部,其中y0是必选项。
程序如下:……
dim s1,s2,s3……
y0=request.form("y0")
y1=request.form("y1")
if y1="0" then
s1=""
else
s1=" and x1='"&y1&"'"
end if
……
y9=request.form("y9")
if y9="0" then
s9=""
else
s9=" and x9='"&y9&"'"
end ifmyabc="x0='"&y0&"'"&s1&s2&s3&……&s9set rs=server.createobject("ADODB.recordset")
sq="select jieguo from abc where "&myabc
rs.open sq,conn,1,3
……
那么myabc="x0='250601' and x1='32'"
这样sq="select jieguo from abc where x0='250601' and x1='32'"
以上程序是ASP中的,运行没问题,现在我想改为SQL存储过程,不知道怎样在存储过程中用一句select来完成。
列:x0 x1 x2 x3 x4 x5 x6 x7 x8 x9 jieguo
request.form y0……y9 10个选择框参数,不选则为0,0表示全部,其中y0是必选项。
x0 as 字段类型(例如int),
x1 as 字段类型(例如int),
x2 as 字段类型(例如int),
x3 as 字段类型(例如int),
x4 as 字段类型(例如int),
x4 as 字段类型(例如int),
x6 as 字段类型(例如int),
x7 as 字段类型(例如int),
x8 as 字段类型(例如int),
x9 as 字段类型(例如int)
begin
select * from tb where
x0 = isnull(@y0 , x0) and
x1 = isnull(@y1 , x1) and
x2 = isnull(@y2 , x2) and
x3 = isnull(@y3 , x3) and
x4 = isnull(@y4 , x4) and
x5 = isnull(@y5 , x5) and
x6 = isnull(@y6 , x6) and
x7 = isnull(@y7 , x7) and
x8 = isnull(@y8 , x8) and
x9 = isnull(@y9 , x9)
end
@s0 varchar(10), --@s0 传进来的值为'250601'
@s1 int, --32
@s2 int, --0
@s3 int,
@s4 int,
@s5 int,
@s6 int,
@s7 int,
@s8 int,
@s9 int
asdeclare @w varchar(1000)if @s1<>0
set @w=' where x0='+@s0+' and x1='+ltrim(@s1)
else
set @w=' where x0='+@s0if @s2<>0
set @w=@w+' and x2='+ltrim(@s2)if @s3<>0
set @w=@w+' and x3='+ltrim(@s3)
if @s4<>0
set @w=@w+' and x4='+ltrim(@s4)if @s5<>0
set @w=@w+' and x5='+ltrim(@s5)if @s6<>0
set @w=@w+' and x6='+ltrim(@s6)if @s7<>0
set @w=@w+' and x7='+ltrim(@s7)if @s8<>0
set @w=@w+' and x8='+ltrim(@s8)if @s9<>0
set @w=@w+' and x9='+ltrim(@s9)
set @w=' select jieguo from abc '+@wexec(@w)go
sq="exec pp"
rs.open sq,conn,1,3
调用数据集时出现:ADODB.Recordset 错误 '800a0e78' 对象关闭时,不允许操作。