我的存储过程是这样写的
create procedure EvalUser
@User varchar(10)
as
begin
if @User=1
select * from evallist where stu=1
if @User=2
select * from evallist where self=1
if @User=3
select * from evallist where teach=1
if @User=4
select * from evallist where mgr=1
if @User=5
select * from evallist where parent=1
end
在页面中这样调用没有错误
<%
Dim Rs,Sql,i
i=1
Sql="Exec EvalUser 1"
response.Write(sql)
Set Rs=Server.CreateObject("ADODB.RecordSet")
Rs.Open Sql,Conn,1,1
%>存储过程这样写
create procedure EvalUser2
@User varchar(10)
as
begin
select * from Evallist where @User=1
end
这样调用
<%
Dim Rs,Sql,i
i=1
Sql="Exec EvalUser2 'stu'"
response.Write(sql)
Set Rs=Server.CreateObject("ADODB.RecordSet")
Rs.Open Sql,Conn,1,1
%>则有这样的错误提示,请问应该怎么写啊
Exec EvalUser2 'stu' Microsoft VBScript 编译器错误 错误 '800a03f6' 缺少 'End' /iisHelp/common/500-100.asp,行242 Microsoft OLE DB Provider for SQL Server 错误 '80040e07' 将 varchar 值 'stu' 转换为数据类型为 int 的列时发生语法错误。 /StuEval/Student/StudentEval.asp,行9 表结构如下
CREATE TABLE [EvalList] (
[ID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EVALNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STARTDATE] [datetime] NOT NULL ,
[ENDDATE] [datetime] NOT NULL ,
[STU] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_STU] DEFAULT (1),
[STUWT] [int] NOT NULL ,
[SELF] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_STUEVAL] DEFAULT (1),
[SELFWT] [int] NOT NULL ,
[TEACH] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_TEACH] DEFAULT (1),
[TEACHWT] [int] NOT NULL ,
[MGR] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_MGR] DEFAULT (1),
[MGRWT] [int] NOT NULL ,
[PARENT] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_PARENT] DEFAULT (1),
[PARENTWT] [int] NOT NULL ,
CONSTRAINT [PK_EVALLIST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
create procedure EvalUser
@User varchar(10)
as
begin
if @User=1
select * from evallist where stu=1
if @User=2
select * from evallist where self=1
if @User=3
select * from evallist where teach=1
if @User=4
select * from evallist where mgr=1
if @User=5
select * from evallist where parent=1
end
在页面中这样调用没有错误
<%
Dim Rs,Sql,i
i=1
Sql="Exec EvalUser 1"
response.Write(sql)
Set Rs=Server.CreateObject("ADODB.RecordSet")
Rs.Open Sql,Conn,1,1
%>存储过程这样写
create procedure EvalUser2
@User varchar(10)
as
begin
select * from Evallist where @User=1
end
这样调用
<%
Dim Rs,Sql,i
i=1
Sql="Exec EvalUser2 'stu'"
response.Write(sql)
Set Rs=Server.CreateObject("ADODB.RecordSet")
Rs.Open Sql,Conn,1,1
%>则有这样的错误提示,请问应该怎么写啊
Exec EvalUser2 'stu' Microsoft VBScript 编译器错误 错误 '800a03f6' 缺少 'End' /iisHelp/common/500-100.asp,行242 Microsoft OLE DB Provider for SQL Server 错误 '80040e07' 将 varchar 值 'stu' 转换为数据类型为 int 的列时发生语法错误。 /StuEval/Student/StudentEval.asp,行9 表结构如下
CREATE TABLE [EvalList] (
[ID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EVALNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STARTDATE] [datetime] NOT NULL ,
[ENDDATE] [datetime] NOT NULL ,
[STU] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_STU] DEFAULT (1),
[STUWT] [int] NOT NULL ,
[SELF] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_STUEVAL] DEFAULT (1),
[SELFWT] [int] NOT NULL ,
[TEACH] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_TEACH] DEFAULT (1),
[TEACHWT] [int] NOT NULL ,
[MGR] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_MGR] DEFAULT (1),
[MGRWT] [int] NOT NULL ,
[PARENT] [bit] NOT NULL CONSTRAINT [DF_EVALLIST_PARENT] DEFAULT (1),
[PARENTWT] [int] NOT NULL ,
CONSTRAINT [PK_EVALLIST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
@User varchar(10)
as
begin
if @User=1
select * from evallist where stu='1'
if @User=2
select * from evallist where self=1
if @User=3
select * from evallist where teach=1
if @User=4
select * from evallist where mgr=1
if @User=5
select * from evallist where parent=1
end
create procedure EvalUser2
@User varchar(10)
as
begin
select * from Evallist where @User=1
end
@User varchar(10)
as
begin
exec('select * from Evallist where '+ @User+ '=1')
end
-------------
这样写是不合法的,要用动态sql语句,
@User varchar(10)
as
begin
select * from Evallist where @User=1
end----------------------------------------------
create procedure EvalUser2
@User varchar(10)
as
begin
DECLARE @SQL VARCHAR(100)
SET @SQL ='select * from Evallist where'+ @User+'=1'
EXEC(@SQL)
end
@User varchar(10)
as
begin
select * from Evallist where @User='1'
end
Dim Rs,Sql,i
Dim stu
stu=1
i=1
Sql="Exec EvalUser2 "&stu
response.Write(sql)
Set Rs=Server.CreateObject("ADODB.RecordSet")
Rs.Open Sql,Conn,1,1
%>