用exec sp_executesql @sql,N'@b int output',@b output 取出查询到@sql的数

 

这是我的理解，请各位品评、品评

 

USE [wwdthdb00]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[wwdadm_lottery]
(

//这里是声明全局变量
@F_member_id varchar(12)

)
AS
begin

//这里是声明局部变量
declare @sql nvarchar(1000),@sq nvarchar(1000),@F_cm1 int,@F_cm2 int,@b nvarchar(100),@F_field_set_pm int,@F_pm3 int,@Sui_ji_count int,@F_cm int,@a nvarchar(100),@F_debt2 money,@F_month_cm_id int,@F_field_set_id int
begin transaction
   set @F_cm=(SELECT F_cm FROM T_member_information where  F_cm>=1 and F_member_id=@F_member_id)
   set @F_month_cm_id=(SELECT F_month_cm_id FROM T_month_cm where F_flag='0')
   set @F_cm1=(@F_cm/10)
   set @F_cm2=(@F_cm%10)
   set @Sui_ji_count=cast(ceiling(rand()*5)as int)               //这里是使用随机函数并将其转为int型
   set @F_field_set_pm =(select F_field_set_pm from T_lottery_field_set where F_lottery_id=@Sui_ji_count)--抽到的对应F_field_set_pm
   set @sql='set @b=(select F_'+cast(@Sui_ji_count as varchar(10))+' from T_lottery_details where F_lottery_id='+cast(@F_cm2 as varchar(100))+')'--小于10时的字段值
   exec sp_executesql @sql,N'@b int output',@b output       //用exec sp_executesql取出查询到@sql语句的数并存到变量@b

  set @sq='set @a=(select F_'+cast(@Sui_ji_count as varchar(10))+' from T_lottery_details where F_lottery_id='+cast(10 as varchar(100))+')'--等于10或者大于10查找到的字段值
   exec sp_executesql @sq,N'@a int output',@a output     //用exec sp_executesql取出查询到的数并存到变量@a

   set @F_debt2=(select F_debt from [T_member_information] where F_member_id=@F_member_id)
if @F_cm>10
begin
 set @F_pm3=@b*@F_field_set_pm+@F_cm1*@a*@F_field_set_pm          //使用变量@a和@b

     ---//以上是大于11或者等于11F_CM的
end

else

  //以下是sql sever的常规语句与使用exec sp_executesql没什么关系
begin
if @F_cm=10 --大于等于10时
set @F_pm3=@F_cm1*@a*@F_field_set_pm
else --小于10
set @F_pm3=@b*@F_field_set_pm
end

  if(@F_debt2<0)---欠款
begin
    if(@F_pm3>=(-@F_debt2))
UPDATE [T_member_information] set F_cm=F_cm-@F_cm1*10-@F_cm2,F_pm=(F_pm+@F_pm3+@F_debt2),F_debt=0 where F_member_id=@F_member_id
    else
UPDATE [T_member_information] set F_cm=F_cm-@F_cm1*10-@F_cm2,F_debt=(F_debt+@F_pm3) where F_member_id=@F_member_id
end
 else
  begin
UPDATE [T_member_information] set F_cm=F_cm-@F_cm1*10-@F_cm2,F_pm=F_pm+@F_pm3 where F_member_id=@F_member_id
  end       
INSERT INTO T_member_lottery([F_month_cm_id],[F_member_id],[F_field_set_id],[F_member_lottery_cm],F_flag) VALUES(@F_month_cm_id, @F_member_id, @F_field_set_id, @F_field_set_pm,'1')  
    if @@error=0
    begin
    commit
        --return 0
    end
    else
    begin
        rollback
        --return 1
end
end