分确实不多了, 还望见谅
我把问题简化一下, 就是同样的存储过程, 在调试时候单个执行是正确的, 但是, 在C++ 程序调用getfieldvalue的时候只能拿到默认值。请帮我看下是不是存储过程的问题。
这是我的存储过程, 其中使用了表变量, 取值的时候都是nolock形式, 功能是根据一个随机值判断 抽到的奖的号码。
在Visual Studio中调试是正常的。
在NaviCat中单个执行也是EXEC GSP_GP_GetPrizeItemNum 14699, 3760, '11' 也是没有问题的。
但是如果用
这个语句的话
DECLARE @DayDif INT
DECLARE @Res INT
set @DayDif=1000
WHILE @DayDif<10001
BEGIN
EXEC GSP_GP_GetPrizeItemNum 14699, @DayDif, 'aa'
SET @[email protected]+1000
END
使用第一次渠道的结果都是正常的,
但是这个循环执行了一次之后拿到的值, 以后再执行这个存储过程拿到的结果都是默认值。
这个存储过程是我在一个C++程序中调用的, 这个就是用getfieldvalue, 这时候拿到的值第一次是个3, 其次就都是默认值了。
请问怎么回事。
我猜是
1 存储过程提交值的过程可能会有一些问题
2 存储过程中的表变量和nolock设置也会有一些影响,
但是不具备相关的知识, 希望各位高手指点一下。
ALTER PROCEDURE [dbo].[GSP_GP_GetPrizeItemNum]
@UserID INT,
@RandNum INT,
@strErrorDescribe NVARCHAR(127) OUTPUT--输出信息
AS
-- 属性设置
SET NOCOUNT ON
-- 基本信息
DECLARE @Pool BIGINT
DECLARE @MaxStorage BIGINT
DECLARE @BaseNumber BIGINT
DECLARE @Lucky FLOAT
DECLARE @LuckyCount INT
DECLARE @NextItem INT
DECLARE @FreeCount INT
DECLARE @PrizeItemID INT
DECLARE @Reward BIGINT
DECLARE @ScoreNum INT
DECLARE @Probability BIGINT
DECLARE @Ext FLOAT
DECLARE @_BS FLOAT
DECLARE @_SUM FLOAT
DECLARE @ReturnValue INT
DECLARE @IsLuckyNull BIT
DECLARE @IsListItemNull BIT
DECLARE @Name NVARCHAR(127)
DECLARE @TimeNow datetime
DECLARE @_temp BIGINT
DECLARE @Probability_SUM BIGINT
--表变量
DECLARE @t_PrizeList_TEMP table
(
ID INT,
Name NVARCHAR(31),
Rank INT,
Reward BIGINT,
Probability INT
)
DECLARE @t_PrizeList table
(
ID INT,
Name NVARCHAR(31),
Rank INT,
Reward BIGINT,
Probability INT
)
DECLARE @t_PrizeList_DESC table
(
ID INT,
Name NVARCHAR(31),
Rank INT,
Reward BIGINT,
Probability INT
)
DECLARE @t_Lucky table
(
ID INT,
UserID INT,
Lucky DECIMAL,
NextItem INT,
LuckyCount INT,
FreeCount INT
)
--DEL
DECLARE @t_PrizeItem table
(
ID INT
)
SET @Ext=1
SET @Lucky=1
SET @TimeNow=GETDATE()
SET @IsLuckyNull=0
SET @IsListItemNull=0
SET @_SUM=0
BEGIN
--初始化配置Config PrizeList 和 Lucky
SELECT @Pool=Pool, @MaxStorage=MaxStorage, @BaseNumber=BaseNumber, @ScoreNum=ScoreNum FROM QPAccountsDB.dbo.PrizeConfig(NOLOCK)
--初始化奖项为最低奖
SET @PrizeItemID=13
--奖励数额应该小于奖励池中的数额
INSERT @t_PrizeList_TEMP SELECT ID, Name, Rank, Reward, Probability FROM QPAccountsDB.dbo.PrizeItem(NOLOCK)
UPDATE @t_PrizeList_TEMP SET Reward = Reward * @ScoreNum WHERE Reward < 10
INSERT @t_PrizeList SELECT ID, Name, Rank, Reward, Probability FROM @t_PrizeList_TEMP WHERE Reward<@Pool ORDER BY Rank
[email protected] and FreeCount>0
IF EXISTS (SELECT * FROM QPAccountsDB.dbo.PrizeItemLucky(NOLOCK) Where [email protected] and FreeCount>0)
BEGIN
SELECT @Lucky=Lucky, @NextItem=NextItem, @LuckyCount=LuckyCount, @FreeCount=FreeCount FROM QPAccountsDB.dbo.PrizeItemLucky(NOLOCK) Where [email protected] AND FreeCount>0
INSERT @t_Lucky SELECT ID, UserID, Lucky, NextItem, LuckyCount, FreeCount FROM QPAccountsDB.dbo.PrizeItemLucky(NOLOCK) Where [email protected] and FreeCount>0
END
ELSE
BEGIN
SET @IsLuckyNull=1
END
IF NOT EXISTS (SELECT * FROM @t_PrizeList)
BEGIN
SET @strErrorDescribe='奖励池金币不足'
SET @ReturnValue=35
END
ELSE
BEGIN
--幸运值设置
IF @IsLuckyNull=0 AND @Lucky <> 1 AND @LuckyCount > 0
BEGIN
SET @[email protected];
IF (@LuckyCount = 1)
BEGIN
SET @Lucky = 1;
--UPDATE PrizeItemLucky SET Lucky = 1 WHERE UserID = @UserID
END
END
--概率计算
SET @Ext = (1.0 * @Pool) / @MaxStorage * @Lucky;--最终中奖概率
SELECT @Probability_SUM=SUM(ISNULL(Probability,0)) FROM QPAccountsDB.dbo.PrizeItem
SET @_BS = 1.0 * 10000 * @Ext;
SET @[email protected][email protected]
--SET @RandNum=CAST(LEFT(RAND()*10000,4) AS INT)
--控制下次中奖
IF @IsLuckyNull=0 AND @NextItem <> -1
BEGIN
IF EXISTS (SELECT TOP 1 * FROM @t_PrizeList WHERE ID>[email protected] ORDER BY Rank)
BEGIN
SELECT TOP 1 @PrizeItemID=ID, @Reward=Reward, @Name=Name FROM @t_PrizeList WHERE ID>[email protected] ORDER BY Rank
END
ELSE
BEGIN
SET @IsListItemNull=1
END
SET @NextItem = -1;
--UPDATE PrizeItemLucky SET NextItem = -1 WHERE UserID = @UserID
END
ELSE
BEGIN
DECLARE @error INT
DECLARE @tempID varchar(50)--临时变量，用来保存游标值
SET @error=0
BEGIN TRAN --申明事务
--申明游标 为userid
INSERT @t_PrizeList_DESC SELECT ID, Name, Rank, Reward, Probability FROM @t_PrizeList ORDER BY RANK DESC
--SELECT * FROM @t_PrizeList_DESC
DECLARE order_cursor CURSOR FOR SELECT ID FROM @t_PrizeList_DESC
--打开游标
OPEN order_cursor
WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
BEGIN
--开始循环游标变量
FETCH NEXT FROM order_cursor INTO @tempID
--执行sql操作
SELECT @Probability=Probability, @PrizeItemID=ID, @Reward=Reward, @Name=Name FROM @t_PrizeList_DESC WHERE [email protected]
SET @_temp = @_SUM;
SET @_SUM += 1.0 * @Probability * @_BS;
IF EXISTS (SELECT * FROM @t_PrizeList_DESC WHERE [email protected]) AND (@RandNum > @_temp AND @RandNum <= @_SUM)
BEGIN
SET @IsListItemNull=0
SELECT @Probability=Probability, @PrizeItemID=ID, @Reward=Reward, @Name=Name FROM @t_PrizeList_DESC WHERE [email protected] AND @RandNum > @_temp AND @RandNum <= @_SUM
GOTO OUTLOOP
END
SET @[email protected]+@@error --记录每次运行sql后 是否正确 0正确
END
OUTLOOP:
IF @error=0 --没有错误 统一提交事务
BEGIN
COMMIT tran --提交
END
ELSE
BEGIN
ROLLBACK tran--回滚
END
CLOSE order_cursor--关闭游标
DEALLOCATE order_cursor--释放游标
IF @IsLuckyNull=0--更新用掉之后的幸运值信息
BEGIN
UPDATE QPAccountsDB.dbo.PrizeItemLucky SET Lucky = @Lucky, NextItem = @NextItem WHERE UserID = @UserID
END
SET @strErrorDescribe='获取PrizeItem 成功'
SET @ReturnValue=34
END
SELECT @Probability AS Probability, @PrizeItemID AS PrizeItemID, @Reward AS Reward, @Name AS Name
RETURN @ReturnValue
END
END
------解决思路----------------------
------解决思路----------------------
楼主是在用嵌入式SQL吗？