Sunday, 18 November 2018

SQL: How to execute a variable as a query

Sample:


       DECLARE @StakeFC DECIMAL(19,4) = 2.5
       DECLARE @PayoutFC DECIMAL(19,4) = 0
       DECLARE @POCTaxRate DECIMAL(19,4) = 0
       DECLARE @POCTaxRule nVARCHAR(500)
     
       declare @PayoutSomething decimal(19,4)

     
       select @POCTaxRate = 4.000, @POCTaxRule = '(ISNULL(CAST(@StakeFC AS DECIMAL(19,4)),0.0) - ISNULL(CAST(@PayoutFC AS DECIMAL(19,4)),0.0))'
 
   declare @sql nvarchar(4000)
   set @sql = 'SELECT @PayoutSomething = (ISNULL(CAST(@StakeFC AS DECIMAL(19,4)),0.0) - ISNULL(CAST(@PayoutFC AS DECIMAL(19,4)),0.0))'
 
   declare @ParamDefinition nvarchar(500)
   set @ParamDefinition = N'@StakeFC decimal(19,4), @PayoutFC decimal(19,4), @PayoutSomething decimal(19,4) OUTPUT'
   execute sp_executesql @sql, @ParamDefinition, @StakeFC = @StakeFC, @PayoutFC = @PayoutFC, @PayoutSomething = @PayoutSomething output

   select @PayoutSomething