aLTER PROCEDURE DelUsers
(
@Id int --Id
)
AS
SET NOCOUNT ON
DECLARE @username nvarchar(50),@uid int
SELECT @username=[username] from [Users] where id=@Id
--DELETE from [Users] where id=@Id
SELECT @uid=uid from ZXXfudaoBBS.dbo.jhbbs_users where [username]=@username
IF @uid>0
BEGIN
/*
DELETE from ZXXfudaoBBS.dbo.jhbbs_users where [uid]=@uid
DELETE from ZXXfudaoBBS.dbo.jhbbs_userfields where [uid]=@uid
DELETE from ZXXfudaoBBS.dbo.jhbbs_polls where [userid]=@uid
DELETE from ZXXfudaoBBS.dbo.jhbbs_favorites where [uid]=@uid
DELETE from ZXXfudaoBBS.dbo.jhbbs_topics where [posterid]=@uid
DELETE from ZXXfudaoBBS.dbo.jhbbs_pms where [msgfromid]=@uid
UPDATE ZXXfudaoBBS.dbo.jhbbs_statistics SET [totalusers]=[totalusers]-1
DECLARE @luid int,@lusername varchar(20)
SELECT @luid=[uid],@lusername=[username] from ZXXfudaoBBS.dbo.jhbbs_users order by [uid] desc
IF @lusername<>null
UPDATE ZXXfudaoBBS.dbo.jhbbs_statistics SET [lastuserid]=@luid,[lastusername]=@lusername
*/
declare @sqltxt nvarchar(4000),@tablename nvarchar(20)
declare rs cursor for select [description] from ZXXfudaoBBS.dbo.jhbbs_tablelist
open rs
fetch next from rs into @tablename
while @@fetch_status=0
begin
--错就在这里,@uid一直传不进去。。郁闷死
set @sqltxt='DELETE from ZXXfudaoBBS.dbo.jhbbs_'+@tablename+' where [posterid]='''+@uid+''
--EXEC (@sqltxt)
print @sqltxt
fetch next from rs into @tablename
end
close rs
deallocate rs
END
set @sqltxt='DELETE from ZXXfudaoBBS.dbo.jhbbs_'+@tablename+' where [posterid]='+@uid)
--改为:
set @sqltxt='DELETE from ZXXfudaoBBS.dbo.jhbbs_'+@tablename+' where [posterid]='+ltrim(@uid)
运行[dbo].[DelUsers] ( @Id = 8 ).将 nvarchar 值 'DELETE from ZXXfudaoBBS.dbo.jhbbs_posts1 where [posterid]='' 转换为数据类型为 int 的列时发生语法错误。
没有行受影响。
(返回 0 行)
@RETURN_VALUE =
完成 [dbo].[DelUsers] 运行。
where [posterid]='''+@uid+'' --加引号
如:pt1314917改成where [posterid]='+ltrim(@uid)
就好了。。不过这是为什么呢,@uid本来就是int型的
set @sqltxt='DELETE from ZXXfudaoBBS.dbo.jhbbs_'+@tablename+' where [posterid]=' + cast(@uid as varchar(15))
set @sqltxt 的时候只组合动态sql 语句,这个时候,要把 @uid 处理成字符类型的,字符串才能相加