) As DECLARE @name Varchar(50) DECLARE @actualPassword Varchar( 50 ) DECLARE @temptable nVarchar(4000) set @temptable = N'SELECT @name=username,@actualPassword=password FROM '+@zone+N' WHERE telnumber ='''+@telnum+N'''' exec sp_executesql @temptable,N'@name varchar(50) output,@actualPassword varchar(50) output',@name output,@actualPassword outputIF @name is not null IF @password=@actualPassword RETURN 0 ELSE RETURN 2 ELSE RETURN 1
happydreamer(黑DD)哥们,还是出现上面的错误阿!
呵呵!终于OK了!力哥,太谢谢了! 再请教一下,请解释一下 set @temptable = N'SELECT @name=username,@actualPassword=password FROM '+@zone+N' WHERE telnumber ='''+@telnum+N'''' 中N'和'的用法.
--'的用法: select '''','''''',char(39) --N,用nvarchar,nchar,ntext在常量前必须加N,一般在中文系统上可以不加但如果如: create database mmm go use mmm go ALTER DATABASE mmm COLLATE Latin1_General_Ci_Ai go create table test (a nvarchar(100)) go insert test values(N'大力') go select * from test where a=N'大力' select * from test where a='大力'加N不加N就会很明显!
改成:
exec sp_executesql @temptable
2 exec (@temptable )
@telnum Varchar( 50 ),
@password Varchar( 50 ),
@zone Varchar(10)
)
As
CREATE TABLE #TABLE (username VARCHAR(50) NULL,password Varchar( 50 ) NULL)DECLARE @name Varchar(50)
DECLARE @actualPassword Varchar( 50 )
DECLARE @temptable Varchar(100)
set @temptable = 'SELECT username,password FROM '+@zone+'WHERE telnumber = '+@telnumexec (@temptable)SELECT @name=username,@actualPassword =password FROM #TABLE IF @name is not null
IF @password = @actualPassword
RETURN 0
ELSE
RETURN 2
ELSE
RETURN 1
set @temptable = 'SELECT '+@name+' = username,'+@actualPassword+' = password FROM '+@zone+' WHERE telnumber = '+@telnumWhere前面加空格
这句是在一个语句中同时赋值@name和@actualPassword两个变量而前面又只赋值给一个变量 set @temptable,明显是错的。
应该把变量分别赋值两次
set @temptable1 = 'SELECT '+@name+' = username FROM '+@zone+' WHERE telnumber = '+ @telnum
declare @temptable2 varchar(50)
set @temptable2 = 'SELECT '+@actualPassword+' = password FROM '+@zone+' WHERE telnumber = '+@telnum
错误提示
服务器: 消息 214,级别 16,状态 2,过程 sp_executesql,行 23
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。
caiyunxia(monkey):
错误提示
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '=' 附近有语法错误。
存储过程: zone.dbo.CheckValid
返回代码 = 1xpilot(飞行石)
错误提示
服务器: 消息 2812,级别 16,状态 62,过程 CheckValid,行 19
未能找到存储过程 ''。
服务器: 消息 2812,级别 16,状态 62,过程 CheckValid,行 22
未能找到存储过程 ''。
存储过程: zone.dbo.CheckValid
返回代码 = 1救命啊!!!
@telnum Varchar( 50 ),
@password Varchar( 50 ),
@zone Varchar(10)
)
As
DECLARE @name Varchar(50)
DECLARE @actualPassword Varchar( 50 )
DECLARE @temptable nVarchar(4000
set @temptable = N'SELECT @name= username,@actualPassword= password FROM '+@zone+'WHERE telnumber = '''+@telnum+''''exec sp_executesql @sql,N'@name varchar(50) output',N'@actualPassword varchar(50)',@name output,@actualPassword outputIF @name is not null
IF @password = @actualPassword
RETURN 0
ELSE
RETURN 2
ELSE
RETURN 1
@telnum Varchar( 50 ),
@password Varchar( 50 ),
@zone Varchar(10)
)
As
DECLARE @name Varchar(50)
DECLARE @actualPassword Varchar( 50 )
DECLARE @temptable nVarchar(4000)
set @temptable = N'SELECT @name= username,@actualPassword= password FROM '+@zone+'WHERE telnumber = '''+@telnum+''''exec sp_executesql @temptable,N'@name varchar(50) output',N'@actualPassword varchar(50)',@name output,@actualPassword outputIF @name is not null
IF @password = @actualPassword
RETURN 0
ELSE
RETURN 2
ELSE
RETURN 1
错误提示服务器: 消息 137,级别 15,状态 1,行 1
必须声明变量 '@actualPassword'。
存储过程: zone.dbo.CheckValid
返回代码 = 1怎末办啊?
@telnum Varchar( 50 ),
@password Varchar( 50 ),
@zone Varchar(10)
)
As
DECLARE @name Varchar(50)
DECLARE @actualPassword Varchar( 50 )
DECLARE @temptable nVarchar(4000)
set @temptable = N'SELECT @name=username,@actualPassword=password FROM '+@zone+N' WHERE telnumber ='''+@telnum+N''''exec sp_executesql @temptable,N'@name varchar(50) output',N'@actualPassword varchar(50)',@name output,@actualPassword outputIF @name is not null
IF @password=@actualPassword
RETURN 0
ELSE
RETURN 2
ELSE
RETURN 1
必须声明变量 '@actualPassword'。
存储过程: zone.dbo.CheckValid
返回代码 = 1vs.net 下输入参数提示
运行dbo."CheckValid" ( @telnum = a, @password = a, @zone = bjinfo ).必须声明变量 '@actualPassword'。
@telnum Varchar( 50 ),
@password Varchar( 50 ),
@zone Varchar(10)
)
As
DECLARE @name Varchar(50)
DECLARE @actualPassword Varchar( 50 )
DECLARE @temptable nVarchar(4000)
set @temptable = N'SELECT @name=username,@actualPassword=password FROM '+@zone+N' WHERE telnumber ='''+@telnum+N''''
exec sp_executesql @temptable,N'@name varchar(50) output,@actualPassword varchar(50) output',@name output,@actualPassword outputIF @name is not null
IF @password=@actualPassword
RETURN 0
ELSE
RETURN 2
ELSE
RETURN 1
再请教一下,请解释一下
set @temptable = N'SELECT @name=username,@actualPassword=password FROM '+@zone+N' WHERE telnumber ='''+@telnum+N''''
中N'和'的用法.
select '''','''''',char(39)
--N,用nvarchar,nchar,ntext在常量前必须加N,一般在中文系统上可以不加但如果如:
create database mmm
go
use mmm
go
ALTER DATABASE mmm COLLATE Latin1_General_Ci_Ai
go
create table test (a nvarchar(100))
go
insert test values(N'大力')
go
select * from test where a=N'大力'
select * from test where a='大力'加N不加N就会很明显!