UC知道谁帮我把asp中的SQL语句转换到存储过程中
来源：百度知道 编辑：UC知道 时间：2024/06/30 09:53:36
我要实现的功能是，asp通过页面检索条件值传入到存储过程，进行条件判断查询数据库，最后输出查询结果：
asp中的检索代码如下：
flag=request("flag")
name=request("name")
class=request("class")
age=request("age")
if flag=1 then
mstrSQL = mstrSQL + "select * from person where 1=1 "
If name <> "" Then
mstrSQL = mstrSQL + " and name = '" & name & "'"
End If
If class <> "" Then
mstrSQL = mstrSQL + " and class = '" & class & "'"
End If
If age <> "" Then
mstrSQL = mstrSQL + " and age = '" & age & "'"
End If
else
mstrSQL = mstrSQL + "select * from person where vm='action'"
end if
我现在想把这些asp里写的SQL语句全部写到存储过程里：
是这样写吗：
CREATE PROCEDURE Proc_person
(
@flag int,
@name
asp中的检索代码如下：
flag=request("flag")
name=request("name")
class=request("class")
age=request("age")
if flag=1 then
mstrSQL = mstrSQL + "select * from person where 1=1 "
If name <> "" Then
mstrSQL = mstrSQL + " and name = '" & name & "'"
End If
If class <> "" Then
mstrSQL = mstrSQL + " and class = '" & class & "'"
End If
If age <> "" Then
mstrSQL = mstrSQL + " and age = '" & age & "'"
End If
else
mstrSQL = mstrSQL + "select * from person where vm='action'"
end if
我现在想把这些asp里写的SQL语句全部写到存储过程里：
是这样写吗：
CREATE PROCEDURE Proc_person
(
@flag int,
@name
如果写在存储过程里需要使用动态语句，如下
CREATE PROCEDURE Proc_person
(
@flag int,
@name varchar(10),
@class varchar(10),
@age int
)
As
declare @sqlstr varchar(8000)
set @sqlstr=''
if (@flag=0)
begin
set @sqlstr='select * from person where 1=1 '
if @name <> ''
set @sqlstr=@sqlstr+' and name = '''+@name +''''
if @class <> ''
set @sqlstr=@sqlstr+' and name = '''+@class +''''
if @age > 0
set @sqlstr=@sqlstr+' and age = '+cast(@age as varchar(10))
end
else
begin
set @sqlstr='select * from person where vm=''action'''
end
exec(@sqlstr)