每次搞存储过程,好多不会,尝试写了一个,运行不太正常,请教问题出在哪。USE [db]
GO
/****** Object: StoredProcedure [dbo].[XMUserAllot] Script Date: 2013/8/29 23:29:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[XMUserAllot]
@UserName varchar(25)
AS
begin
SET NOCOUNT ON;
declare @UserId int,
@ThisUser varchar(25),
@AllotCount smallint,
@RightCount smallint,
@LeftCount smallint
if(@UserName is null)
declare XM_Cursor cursor forward_only static for select [username],[defaultshouquan] from ShouQuanUsers where defaultshouquan>0
else
declare XM_Cursor cursor forward_only static for select [username],[defaultshouquan] from ShouQuanUsers where defaultshouquan>0 and [username]=@UserName
open XM_Cursor
fetch next from XM_Cursor into @ThisUser,@AllotCount
while(@@FETCH_STATUS=0)
begin
select @UserId=[Id] from [User] where UserName=@ThisUser
if(@UserId is not null)
begin
select @RightCount=count(*) from XMUsers where userid=@UserId
set @LeftCount= @AllotCount-@RightCount
if(@LeftCount>0)
update top(@LeftCount) XMUsers set userid=@UserId where userid is null
end
fetch next from XM_Cursor into @ThisUser,@AllotCount
set @UserId=null
end
close XM_Cursor
deallocate XM_Cursor
end
代码功能是按照表ShouQuanUsers记录每个用户应该分配到的商品数量,给用户分配礼品。现在的情况如果这个存储过程传递单一用户名进去,给这个用户分配的礼品是正常的,如果传递null进去,按道理来说是给所有用户进行礼品的分配,但是实际上并没有给所有用户进行分配,有的分了有的没分,分到的也没有分够设定的数量,这是怎么回事?我实在是搞不明白
GO
/****** Object: StoredProcedure [dbo].[XMUserAllot] Script Date: 2013/8/29 23:29:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[XMUserAllot]
@UserName varchar(25)
AS
begin
SET NOCOUNT ON;
declare @UserId int,
@ThisUser varchar(25),
@AllotCount smallint,
@RightCount smallint,
@LeftCount smallint
if(@UserName is null)
declare XM_Cursor cursor forward_only static for select [username],[defaultshouquan] from ShouQuanUsers where defaultshouquan>0
else
declare XM_Cursor cursor forward_only static for select [username],[defaultshouquan] from ShouQuanUsers where defaultshouquan>0 and [username]=@UserName
open XM_Cursor
fetch next from XM_Cursor into @ThisUser,@AllotCount
while(@@FETCH_STATUS=0)
begin
select @UserId=[Id] from [User] where UserName=@ThisUser
if(@UserId is not null)
begin
select @RightCount=count(*) from XMUsers where userid=@UserId
set @LeftCount= @AllotCount-@RightCount
if(@LeftCount>0)
update top(@LeftCount) XMUsers set userid=@UserId where userid is null
end
fetch next from XM_Cursor into @ThisUser,@AllotCount
set @UserId=null
end
close XM_Cursor
deallocate XM_Cursor
end
代码功能是按照表ShouQuanUsers记录每个用户应该分配到的商品数量,给用户分配礼品。现在的情况如果这个存储过程传递单一用户名进去,给这个用户分配的礼品是正常的,如果传递null进去,按道理来说是给所有用户进行礼品的分配,但是实际上并没有给所有用户进行分配,有的分了有的没分,分到的也没有分够设定的数量,这是怎么回事?我实在是搞不明白
解决方案 »
- 新手求教 关于SQl Server2000 和SQl Server2005 数据附加的问题
- 有些配置页面是不是就一定使用的web服务器?
- 用hibernate连接两个数据库
- SQL SERVER 2005存储过程调试方法
- 在嵌套的游标中给变量赋值老是报将数据类型varchar转换为 numeric 时出错。 谢谢各位帮小弟看一下,急呀!非常感谢
- SOS,如何用sql实现这样的功能?
- 动态纵表变横表,max里面的变量为什么加三个'''+变量+'''分号,加一个报错?
- sql server 2000+sql mobile 2005合并复制问题
- 同时安装sql2000,sql2005,无法连接到sql 2005,在线等!!!急!!!
- 怎么最快的取表中的行数?
- Sql server 2008 循环表数据的问题
- 用过reporting service 的来下。。
CREATE PROCEDURE [dbo].[XMUserAllot] @UserName VARCHAR(25)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserId INT ,
@ThisUser VARCHAR(25) ,
@AllotCount SMALLINT ,
@RightCount SMALLINT ,
@LeftCount SMALLINT
DECLARE XM_Cursor CURSOR forward_only static
FOR
SELECT [username] ,
[defaultshouquan]
FROM ShouQuanUsers
WHERE defaultshouquan > 0
AND (@UserName IS NULL OR [username] = @UserName) --改成这样试试 OPEN XM_Cursor
FETCH NEXT FROM XM_Cursor INTO @ThisUser, @AllotCount
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @UserId = [Id]
FROM [User]
WHERE UserName = @ThisUser IF ( @UserId IS NOT NULL )
BEGIN
SELECT @RightCount = COUNT(*)
FROM XMUsers
WHERE userid = @UserId SET @LeftCount = @AllotCount - @RightCount IF ( @LeftCount > 0 )
UPDATE TOP ( @LeftCount ) --要保证XMUsers表有足够userid为NULL的记录
XMUsers
SET userid = @UserId
WHERE userid IS NULL
END
FETCH NEXT FROM XM_Cursor INTO @ThisUser, @AllotCount
SET @UserId = NULL
END
CLOSE XM_Cursor
DEALLOCATE XM_Cursor
END