@username as char(10),@x as int @username 是查找用户名, @X是对@username用户名中的text字段中记录的用户的PUTIN + @X
Create Procedure spGetInfo @UserName char(10), @x intAS Begin SET NOCOUNT ON DECLARE @uname char(10), @info_list varchar(8000) SET @info_list = '' UPDATE un_user SET putin = putin + @x WHERE [text] LIKE @username + ',%' OR [text] LIKE '%,' + @username OR [text] LIKE '%,' + @username + ',%'
DECLARE cur_info CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT i.username FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
OPEN cur_info FETCH NEXT FROM cur_info INTO @uname WHILE @@FETCH_STATUS = 0 BEGIN SET @info_list = @info_list + @uname FETCH NEXT FROM cur_info INTO @uname END CLOSE cur_info DEALLOCATE cur_info UPDATE un_user SET [text] = @info_list WHERE username=@username SELECT i.info FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term SET NOCOUNT OFF End
Create Procedure spGetInfo @UserName varchar(10), @x intAS Begin SET NOCOUNT ON DECLARE @uname char(10), @info_list varchar(8000), @sql varchar(5000) SET @info_list = '' SELECT @sql = 'UPDATE un_user SET putin = putin + ' + CONVERT(varchar, @x) + ' WHERE [text] LIKE ''' + @username + ',%'' OR [text] LIKE ''%,' + @username + ''' OR [text] LIKE ''%,' + @username + ',%''' EXEC(@sql) DECLARE cur_info CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT i.username FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
OPEN cur_info FETCH NEXT FROM cur_info INTO @uname WHILE @@FETCH_STATUS = 0 BEGIN SET @info_list = @info_list + @uname FETCH NEXT FROM cur_info INTO @uname END CLOSE cur_info DEALLOCATE cur_info UPDATE un_user SET [text] = @info_list WHERE username=@username SELECT i.info FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term SET NOCOUNT OFF End
你的数据结构是什么,
写清楚一点
@username 是查找用户名,
@X是对@username用户名中的text字段中记录的用户的PUTIN + @X
@UserName char(10),
@x intAS
Begin
SET NOCOUNT ON
DECLARE @uname char(10), @info_list varchar(8000)
SET @info_list = ''
UPDATE un_user SET putin = putin + @x WHERE [text] LIKE @username + ',%' OR [text] LIKE '%,' + @username OR [text] LIKE '%,' + @username + ',%'
DECLARE cur_info CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT i.username FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
OPEN cur_info
FETCH NEXT FROM cur_info INTO @uname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @info_list = @info_list + @uname
FETCH NEXT FROM cur_info INTO @uname
END
CLOSE cur_info
DEALLOCATE cur_info
UPDATE un_user SET [text] = @info_list WHERE username=@username
SELECT i.info FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
SET NOCOUNT OFF
End
@UserName varchar(10),
@x intAS
Begin
SET NOCOUNT ON
DECLARE @uname char(10), @info_list varchar(8000), @sql varchar(5000)
SET @info_list = ''
SELECT @sql = 'UPDATE un_user SET putin = putin + ' + CONVERT(varchar, @x) + ' WHERE [text] LIKE ''' + @username + ',%'' OR [text] LIKE ''%,' + @username + ''' OR [text] LIKE ''%,' + @username + ',%'''
EXEC(@sql)
DECLARE cur_info CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT i.username FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
OPEN cur_info
FETCH NEXT FROM cur_info INTO @uname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @info_list = @info_list + @uname
FETCH NEXT FROM cur_info INTO @uname
END
CLOSE cur_info
DEALLOCATE cur_info
UPDATE un_user SET [text] = @info_list WHERE username=@username
SELECT i.info FROM info i, un_user u WHERE i.username=@username AND u.username=@username AND u.GetOut-u.PutIN > u.Term
SET NOCOUNT OFF
End