我要实现的功能是,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 varchar(10),
@class varchar(10),
@age int,
)
As
if (@flag=0)
begin
select * from person where 1=1
if @name <> ''
and name = @name
if @class <> ''
and name = @class
if @age > 0
and age = @age
end
else
begin
select * from person where vm='action'
end GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 谁帮我看下,好象有错误!! 谢谢
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 varchar(10),
@class varchar(10),
@age int,
)
As
if (@flag=0)
begin
select * from person where 1=1
if @name <> ''
and name = @name
if @class <> ''
and name = @class
if @age > 0
and age = @age
end
else
begin
select * from person where vm='action'
end GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 谁帮我看下,好象有错误!! 谢谢
CREATE PROCEDURE Proc_person
(
@flag int,
@name varchar(10),
@class varchar(10),
@age int --多个逗号
)
As
if (@flag=0)
begin
select * from person where 1=1 --下面3个if好像没用,而且where 1=1没有用
--if @name <> '' and name = @name
--if @class <> '' and name = @class
--if @age > 0 and age = @age
end
else
begin
select * from person where vm='action'
end
不过其实语句并非如此,远比此复杂,我只是想知道这种写法的格式是否正确
(
@flag INT,
@name VARCHAR(10),
@class VARCHAR(10),
@age INT
)
AS
BEGIN
SELECT * FROM person
WHERE vm=CASE WHEN @flag=1 THEN vm ELSE 'action' END --当flag=1时以不处理vm列,否则以 vm='action'过滤
AND name=ISNULL(NULLIF(@name,''),name) --当@name为null或空白字串时,不处理name列,否则以 name=@name处理.以下同理
AND class=ISNULL(NULLIF(@class,''),class)
AND age=ISNULL(NULLIF(@age,''),age)
END
此话不正确