Stored Procedure: IF...WHERE help

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  February 23, 2011 at 6:11 am

  #247241

  Hi everyone

  I have this stored procedure.

  CREATE PROCEDURE [dbo].[sp_EE_A_disabled]

  @inT1 VARCHAR(200),

  @inT2 VARCHAR(200),

  @inT3 VARCHAR(200)

  AS

  DECLARE @T_DIS INT = 2 --TARGET PERCENTAGE

  DECLARE @A_DIS DECIMAL(7,4) --ACHIEVED %

  DECLARE @DIS_F DECIMAL(10,4) --DISABLED FEMALE

  DECLARE @DIS_M DECIMAL(10,4) --DISABLED MALE

  DECLARE @DIS_N DECIMAL(10,4) --NOT DISABLED

  DECLARE @WHERE VARCHAR(800)

  --WORK OUT AMOUNT FOR EACH SUB GROUP

  SELECT @DIS_F = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'F'

  SELECT @DIS_M = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'M'

  SELECT @DIS_N = COUNT(DISTINCT pNo) FROM bc_Employees

  IF (((@DIS_F/@DIS_N)*100) > (@T_DIS * 0.5))

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2)*100) + (@T_DIS * 0.5)

  ELSE

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2) + (@DIS_F/@DIS_N))*100

  SELECT @A_DIS

  I want to implement the @inT1-3 variables into the SELECT CLAUSES.

  What is the best way to implement this?

  I was thinking along these lines, but would appreciate your input.

  CREATE PROCEDURE [dbo].[sp_EE_A_disabled]

  @inT1 VARCHAR(200),

  @inT2 VARCHAR(200),

  @inT3 VARCHAR(200)

  AS

  DECLARE @T_DIS INT = 2 --TARGET PERCENTAGE

  DECLARE @A_DIS DECIMAL(7,4) --ACHIEVED %

  DECLARE @DIS_F DECIMAL(10,4) --DISABLED FEMALE

  DECLARE @DIS_M DECIMAL(10,4) --DISABLED MALE

  DECLARE @DIS_N DECIMAL(10,4) --NOT DISABLED

  DECLARE @WHERE VARCHAR(800)

  --WORK OUT AMOUNT FOR EACH SUB GROUP

  IF @inT1 = '' AND @inT2 = '' AND @inT3 = ''

  BEGIN

  SELECT @DIS_F = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'F'

  SELECT @DIS_M = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'M'

  SELECT @DIS_N = COUNT(DISTINCT pNo) FROM bc_Employees

  END

  IF @inT1 <> ''

  BEGIN

  SELECT @DIS_F = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'F' AND [t1] = @inT1

  SELECT @DIS_M = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'M' AND [t1] = @inT1

  SELECT @DIS_N = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [t1] = @inT1

  END

  IF @inT2 <> ''

  BEGIN

  SELECT @DIS_F = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'F' AND [t2] = @inT2

  SELECT @DIS_M = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'M' AND [t2] = @inT2

  SELECT @DIS_N = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [t2] = @inT2

  END

  IF @inT3 <> ''

  BEGIN

  SELECT @DIS_F = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'F' AND [t3] = @inT3

  SELECT @DIS_M = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [disabled] = 1 AND [gender] = 'M' AND [t3] = @inT3

  SELECT @DIS_N = COUNT(DISTINCT pNo) FROM bc_Employees WHERE [t3] = @inT3

  END

  IF (((@DIS_F/@DIS_N)*100) > (@T_DIS * 0.5))

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2)*100) + (@T_DIS * 0.5)

  ELSE

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2) + (@DIS_F/@DIS_N))*100

  SELECT @A_DIS

  thanks

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  February 23, 2011 at 6:23 am

  #1290338

  Not looked at your "IF" statement at the end of the proc, but the SELECT statements can be concatenated using CASE statements.

  CREATE PROCEDURE [dbo].[Sp_ee_a_disabled]

  @inT1 VARCHAR(200), @inT2 VARCHAR(200), @inT3 VARCHAR(200)

  AS

  BEGIN

  DECLARE @T_DIS INT = 2 --TARGET PERCENTAGE

  DECLARE @A_DIS DECIMAL(7, 4) --ACHIEVED %

  DECLARE @DIS_F DECIMAL(10, 4) --DISABLED FEMALE

  DECLARE @DIS_M DECIMAL(10, 4) --DISABLED MALE

  DECLARE @DIS_N DECIMAL(10, 4) --NOT DISABLED

  DECLARE @WHERE VARCHAR(800)

  --WORK OUT AMOUNT FOR EACH SUB GROUP

  SELECT @DIS_F = COUNT(DISTINCT pno)

  FROM bc_employees

  WHERE [disabled] = 1

  AND [gender] = 'F'

  AND [t1] = CASE

  WHEN @inT1 <> '' THEN @inT1

  ELSE [t1]

  END

  AND [t2] = CASE

  WHEN @inT2 <> '' THEN @inT2

  ELSE [t2]

  END

  AND [t3] = CASE

  WHEN @inT3 <> '' THEN @inT3

  ELSE [t3]

  END

  SELECT @DIS_M = COUNT(DISTINCT pno)

  FROM bc_employees

  WHERE [disabled] = 1

  AND [gender] = 'M'

  AND [t1] = CASE

  WHEN @inT1 <> '' THEN @inT1

  ELSE [t1]

  END

  AND [t2] = CASE

  WHEN @inT2 <> '' THEN @inT2

  ELSE [t2]

  END

  AND [t3] = CASE

  WHEN @inT3 <> '' THEN @inT3

  ELSE [t3]

  END

  SELECT @DIS_N = COUNT(DISTINCT pno)

  FROM bc_employees

  WHERE [t1] = CASE

  WHEN @inT1 <> '' THEN @inT1

  ELSE [t1]

  END

  AND [t2] = CASE

  WHEN @inT2 <> '' THEN @inT2

  ELSE [t2]

  END

  AND [t3] = CASE

  WHEN @inT3 <> '' THEN @inT3

  ELSE [t3]

  END

  IF ( ( ( @DIS_F / @DIS_N ) * 100 ) > ( @T_DIS * 0.5 ) )

  SET @A_DIS = ( ( ( @DIS_M + @DIS_F ) / @DIS_N / 2 ) * 100 ) +

  ( @T_DIS * 0.5 )

  ELSE

  SET @A_DIS = ( ( ( @DIS_M + @DIS_F ) / @DIS_N / 2 ) +

  ( @DIS_F / @DIS_N )

  ) *

  100

  SELECT @A_DIS

  END

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  February 23, 2011 at 6:26 am

  #1290342

  thanks!!

  🙂

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  February 23, 2011 at 11:57 pm

  #1290750

  Hi, I have created a test procedure with the CASE statements, I am getting a divide by 0 error, this is happening because the rows are not returned properly. Is there a way to trouble shoot these sql statements somehow? When I change it to my IF statements it works 100%

  DECLARE @T_DIS INT = 2 --TARGET PERCENTAGE

  DECLARE @A_DIS DECIMAL(7,4) --ACHIEVED %

  DECLARE @DIS_F DECIMAL(10,4) --DISABLED FEMALE

  DECLARE @DIS_M DECIMAL(10,4) --DISABLED MALE

  DECLARE @DIS_N DECIMAL(10,4) --NOT DISABLED

  DECLARE@inT1 VARCHAR(200) = ''

  DECLARE@inT2 VARCHAR(200) = ''

  DECLARE @inT3 VARCHAR(200) = ''

  --WORK OUT AMOUNT FOR EACH SUB GROUP

  SELECT @DIS_F = COUNT(DISTINCT pNo)

  FROM bc_Employees

  WHERE [disabled] = 1 AND [gender] = 'F' AND [race] <> 'White'

  AND [t1] = CASE WHEN @inT1 = '' THEN [t1] ELSE @inT1 END

  AND [t2] = CASE WHEN @inT2 = '' THEN [t2] ELSE @inT2 END

  AND [t3] = CASE WHEN @inT3 = '' THEN [t3] ELSE @inT3 END

  SELECT @DIS_M = COUNT(DISTINCT pNo)

  FROM bc_Employees

  WHERE [disabled] = 1 AND [gender] = 'M' AND [race] <> 'White'

  AND [t1] = CASE WHEN @inT1 = '' THEN [t1] ELSE @inT1 END

  AND [t2] = CASE WHEN @inT2 = '' THEN [t2] ELSE @inT2 END

  AND [t3] = CASE WHEN @inT3 = '' THEN [t3] ELSE @inT3 END

  SELECT @DIS_N = COUNT(DISTINCT pNo)

  FROM bc_Employees

  WHERE

  [t1] = CASE WHEN @inT1 = '' THEN [t1] ELSE @inT1 END

  AND [t2] = CASE WHEN @inT2 = '' THEN [t2] ELSE @inT2 END

  AND [t3] = CASE WHEN @inT3 = '' THEN [t3] ELSE @inT3 END

  PRINT @DIS_M

  PRINT @DIS_F

  PRINT @DIS_N

  IF (((@DIS_F/@DIS_N)*100) > (@T_DIS * 0.5))

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2)*100) + (@T_DIS * 0.5)

  ELSE

  SET @A_DIS = (((@DIS_M + @DIS_F)/@DIS_N/2) + (@DIS_F/@DIS_N))*100

  SELECT @A_DIS

  Is there way to see what those sql statements look like at runtime? To see which values has populated?