这是我ASP部分代码,主要就是UserID,UserID1两个参数传递进去,UserName,UserName1两个参数传递出来
<%
UserID = 750214806
UserID1 = -582376046
i=0
Set MyComm = Server.CreateObject("ADODB.Command")
MyConStr="driver={SQL Server};server=Hp-nnwpepaw4e1;UID=sa;PWD=sa;Database=num"
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "getUserName"
MyComm.CommandType = 4
MyComm.Prepared = true
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserID1",3,1,4,UserID1)
MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)
MyComm.Parameters.append MyComm.CreateParameter("@UserName1",200,2,40)
MyComm.Execute
UserName = MyComm.Parameters("@UserName").Value
UserName1 = MyComm.Parameters("@UserName1").Value
Set MyComm = Nothing
%>
下面是我的存储过程代码,目的就是对数据库的表进行循环判断,看与UserID和UserID1是否相符,有符合的就中断循环,并输出UserName和UserName1
USE [num]
GO
/****** Object: StoredProcedure [dbo].[getUserName] Script Date: 09/10/2010 12:48:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getUserName]
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 1
while (@i < 1001)
begin
set @s = 'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+@UserID+''
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if UserName = UserID1)
set @UserName=@i
begin
break;
end
set @i=@i+1
end
<%
UserID = 750214806
UserID1 = -582376046
i=0
Set MyComm = Server.CreateObject("ADODB.Command")
MyConStr="driver={SQL Server};server=Hp-nnwpepaw4e1;UID=sa;PWD=sa;Database=num"
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "getUserName"
MyComm.CommandType = 4
MyComm.Prepared = true
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserID1",3,1,4,UserID1)
MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)
MyComm.Parameters.append MyComm.CreateParameter("@UserName1",200,2,40)
MyComm.Execute
UserName = MyComm.Parameters("@UserName").Value
UserName1 = MyComm.Parameters("@UserName1").Value
Set MyComm = Nothing
%>
下面是我的存储过程代码,目的就是对数据库的表进行循环判断,看与UserID和UserID1是否相符,有符合的就中断循环,并输出UserName和UserName1
USE [num]
GO
/****** Object: StoredProcedure [dbo].[getUserName] Script Date: 09/10/2010 12:48:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getUserName]
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 1
while (@i < 1001)
begin
set @s = 'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+@UserID+''
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if UserName = UserID1)
set @UserName=@i
begin
break;
end
set @i=@i+1
end
begin
set @UserName=@i
break;
end
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 1
while (@i < 1001)
begin
set @s = 'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+@UserID+''
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if (@UserName = @UserID1) --此处括号不全,变量未加@
set @UserName=@i
begin
break;
end
set @i=@i+1
end
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 1
while (@i < 2)
begin
set @s = 'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = +@UserID+'
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if (@UserName = @UserID1) --此处括号不全,变量未加@
set @UserName=@i
begin
break;
end
set @i=@i+1
end
我的存储过程是这样的,所有输入和输出参数全部都是整形,提示错误是:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]必须声明标量变量 "@UserID"。