alter procedure adduserinfo @upwd varchar(50),@uname varchar(200),@uemail varchar(200),@usex int,@ucityid int
as
declare @pid int
declare @uid int
declare @firstsql nvarchar(500)
declare @secsql nvarchar(1000)
declare @third nvarchar(200)
declare @foursql nvarchar(200)
set @firstsql='insert into ayPassWord (ayPassWord) values('''+@upwd+''''+')'
EXECUTE sp_executesql @firstsql
set @pid=@@Identityset @secsql='insert into ayUser (username,useremail,sex,ayPassWordID) values ('''+@uname+''','''+@uemail+''','+@usex+','+@pid+','+@ucityid+')'
EXECUTE sp_executesql @secsql
set @uid=@@Identityset @third='insert into ayFriend(userid) values('+@uid+')'
EXECUTE sp_executesql @third
set @foursql='insert into UserRole(userid,roleid,cityid) values('''+@uid+''',1,'''+@ucityid+''')'
EXECUTE sp_executesql @foursql
exec adduserinfo '123456','ronin','[email protected]',1,170
运行报错
------------
服务器: 消息 245,级别 16,状态 1,过程 adduserinfo,行 13
将 varchar 值 'insert into ayUser (username,useremail,sex,ayPassWordID) values ('ronin','[email protected]',' 转换为数据类型为 int 的列时发生语法错误。
-----------
请问怎么改
as
declare @pid int
declare @uid int
declare @firstsql nvarchar(500)
declare @secsql nvarchar(1000)
declare @third nvarchar(200)
declare @foursql nvarchar(200)
set @firstsql='insert into ayPassWord (ayPassWord) values('''+@upwd+''''+')'
EXECUTE sp_executesql @firstsql
set @pid=@@Identityset @secsql='insert into ayUser (username,useremail,sex,ayPassWordID) values ('''+@uname+''','''+@uemail+''','+@usex+','+@pid+','+@ucityid+')'
EXECUTE sp_executesql @secsql
set @uid=@@Identityset @third='insert into ayFriend(userid) values('+@uid+')'
EXECUTE sp_executesql @third
set @foursql='insert into UserRole(userid,roleid,cityid) values('''+@uid+''',1,'''+@ucityid+''')'
EXECUTE sp_executesql @foursql
exec adduserinfo '123456','ronin','[email protected]',1,170
运行报错
------------
服务器: 消息 245,级别 16,状态 1,过程 adduserinfo,行 13
将 varchar 值 'insert into ayUser (username,useremail,sex,ayPassWordID) values ('ronin','[email protected]',' 转换为数据类型为 int 的列时发生语法错误。
-----------
请问怎么改
替换下面的看看呢
@firstsql='insert into ayPassWord (ayPassWord) values('''+@upwd+''')'
是不是写错了?
as
declare @pid int ----密码ID
declare @uid int ----用户ID
declare @firstsql nvarchar(500)
declare @secsql nvarchar(1000)
declare @third nvarchar(200)
declare @foursql nvarchar(200)
set @firstsql='insert into ayPassWord (ayPassWord) values('''+@upwd+''')'
EXECUTE sp_executesql @firstsql
set @pid=@@Identityset @secsql='insert into ayUser (username,useremail,sex,ayPassWordID,cityid) values ('''+@uname+''','''+@uemail+''','+@usex+','+@pid+','+@ucityid+')'
EXECUTE sp_executesql @secsql
set @uid=@@Identityset @third='insert into ayFriend(userid) values('+@uid+')'
EXECUTE sp_executesql @third
set @foursql='insert into UserRole(userid,roleid,cityid) values('''+@uid+''',1,'''+@ucityid+''')'
EXECUTE sp_executesql @foursql
将
set @secsql='insert into ayUser (username,useremail,sex,ayPassWordID) values ('''+@uname+''','''+@uemail+''','+@usex+','+@pid+','+@ucityid+')'
句改为:
set @secsql='insert into ayUser (username,useremail,sex,ayPassWordID) values ('''+@uname+''','''+@uemail+''','+cast(@usex as varchar(1))+','+cast(@pid as varchar(10))+','+cast(@ucityid as varchar(10))+')'后面也有这样的错误,自己看一下