set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[sp_userApplicationList_Update_Sort_InsertBefore]
@userId int,
@currentId int,
@insertId int
as
declare @tempCurrentSort int
declare @tempInsertSort int
declare @strSelectCurrentId nvarchar(500)
declare @strSelectInsertId nvarchar(500)
declare @strUpdate1 nvarchar(500)
declare @strUpdate2 nvarchar(500)set @strSelectCurrentId = 'select @tempCurrentSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @currentId'set @strSelectInsertId = 'select @tempInsertSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @insertId'set @strUpdate1 = 'update userApplicationList
set sort = (select sort from userApplicationList where userId = @userId and userApplicationLeftId = @insertId)
where userId = @userId and userApplicationLeftId = @currentId'exec sp_executesql @strSelectCurrentId, N'@tempCurrentSort int out,@userId int,@currentId int',@tempCurrentSort out,@userId,@currentIdexec sp_executesql @strSelectInsertId, N'@tempInsertSort int out,@userId int,@insertId int',@tempInsertSort out,@userId,@insertIdif @tempCurrentSort < @strSelectInsertId
begin
set @strUpdate2 = 'update userApplicationList set sort = sort - 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectInsertId + ' and userApplicatonLeftId > ' + @strSelectCurrentId
end
else
begin
set @strUpdate2 = 'update userApplicationList set sort = sort + 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectCurrentId + ' and userApplicatonLeftId > ' + @strSelectInsertId
endexec sp_executesql @strUpdate1
exec sp_executesql @strUpdate2
报错提示
消息 245,级别 16,状态 1,过程 sp_userApplicationList_Update_Sort_InsertBefore,第 36 行
在将 nvarchar 值 'select @tempInsertSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @insertId' 转换成数据类型 int 时失败。这个错误怎么改啊谢谢了,
初学者,望高手指点下啊~~~
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[sp_userApplicationList_Update_Sort_InsertBefore]
@userId int,
@currentId int,
@insertId int
as
declare @tempCurrentSort int
declare @tempInsertSort int
declare @strSelectCurrentId nvarchar(500)
declare @strSelectInsertId nvarchar(500)
declare @strUpdate1 nvarchar(500)
declare @strUpdate2 nvarchar(500)set @strSelectCurrentId = 'select @tempCurrentSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @currentId'set @strSelectInsertId = 'select @tempInsertSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @insertId'set @strUpdate1 = 'update userApplicationList
set sort = (select sort from userApplicationList where userId = @userId and userApplicationLeftId = @insertId)
where userId = @userId and userApplicationLeftId = @currentId'exec sp_executesql @strSelectCurrentId, N'@tempCurrentSort int out,@userId int,@currentId int',@tempCurrentSort out,@userId,@currentIdexec sp_executesql @strSelectInsertId, N'@tempInsertSort int out,@userId int,@insertId int',@tempInsertSort out,@userId,@insertIdif @tempCurrentSort < @strSelectInsertId
begin
set @strUpdate2 = 'update userApplicationList set sort = sort - 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectInsertId + ' and userApplicatonLeftId > ' + @strSelectCurrentId
end
else
begin
set @strUpdate2 = 'update userApplicationList set sort = sort + 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectCurrentId + ' and userApplicatonLeftId > ' + @strSelectInsertId
endexec sp_executesql @strUpdate1
exec sp_executesql @strUpdate2
报错提示
消息 245,级别 16,状态 1,过程 sp_userApplicationList_Update_Sort_InsertBefore,第 36 行
在将 nvarchar 值 'select @tempInsertSort = sort
from userApplicationList
where userId = @userId
and userApplicationLeftId = @insertId' 转换成数据类型 int 时失败。这个错误怎么改啊谢谢了,
初学者,望高手指点下啊~~~
from userApplicationList
where userId = '+rtrim(@userId)+'
and userApplicationLeftId = '+rtrim(@currentId) set @strSelectInsertId = 'select @tempInsertSort = sort
from userApplicationList
where userId = '+rtrim(@userId) +'
and userApplicationLeftId = '+rtrim(@insertId)set @strUpdate1 = 'update userApplicationList
set sort =
(select sort
from userApplicationList where userId = @userId and userApplicationLeftId = @insertId)
where userId = '+rtrim(@userId)+' and userApplicationLeftId = '+rtrim(@currentId) exec sp_executesql @strSelectCurrentId, N'@tempCurrentSort int out',@tempCurrentSort out,@userId,@currentId exec sp_executesql @strSelectInsertId, N'@tempInsertSort int out',@tempInsertSort out,@userId,@insertId
set QUOTED_IDENTIFIER ON
go ALTER procedure [dbo].[sp_userApplicationList_Update_Sort_InsertBefore]
@userId int,
@currentId int,
@insertId int
as
declare @tempCurrentSort int
declare @tempInsertSort int
declare @strSelectCurrentId nvarchar(500)
declare @strSelectInsertId nvarchar(500)
declare @strUpdate1 nvarchar(500)
declare @strUpdate2 nvarchar(500) set @strSelectCurrentId = 'select @tempCurrentSort = sort
from userApplicationList
where userId = '''+rtrim(@userId)+'''
and userApplicationLeftId = '''+rtrim(@currentId) +''''--加上引号set @strSelectInsertId = 'select @tempInsertSort = sort
from userApplicationList
where userId = '+rtrim(@userId) +'
and userApplicationLeftId = '''+rtrim(@insertId)+''''set @strUpdate1 = 'update userApplicationList
set sort =
(select sort
from userApplicationList where userId = '+rtrim(@userId)+' and userApplicationLeftId = '''+@insertId+''')
where userId = '+rtrim(@userId)+' and userApplicationLeftId = '''+rtrim(@currentId) +''''exec sp_executesql @strSelectCurrentId, N'@tempCurrentSort int out',@tempCurrentSort out,@userId,@currentId exec sp_executesql @strSelectInsertId, N'@tempInsertSort int out',@tempInsertSort out,@userId,@insertId if @tempCurrentSort < @strSelectInsertId
begin
set @strUpdate2 = 'update userApplicationList set sort = sort - 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectInsertId + ' and userApplicatonLeftId > ''' + @strSelectCurrentId +''''
end
else
begin
set @strUpdate2 = 'update userApplicationList set sort = sort + 1 where userId = ' + @userId + ' and userApplicationLeftId <= ' + @strSelectCurrentId + ' and userApplicatonLeftId > ''' + @strSelectInsertId +''''
end exec sp_executesql @strUpdate1
exec sp_executesql @strUpdate2
报错提示
消息 245,级别 16,状态 1,过程 sp_userApplicationList_Update_Sort_InsertBefore,第 33 行
在将 nvarchar 值 'select @tempInsertSort = sort
from userApplicationList
where userId = 12
and userApplicationLeftId = 6' 转换成数据类型 int 时失败。我有点不明白为什么这两句是差不多的,为什么是下面那句报错,上面的没错~
exec sp_executesql @strSelectCurrentId, N'@tempCurrentSort int out',@tempCurrentSort out,@userId,@currentId exec sp_executesql @strSelectInsertId, N'@tempInsertSort int out',@tempInsertSort out,@userId,@insertId