troubleshooting Question

T-SQL Nested If

Avatar of anordquist
anordquist asked on
Microsoft SQL ServerSQL
3 Comments2 Solutions399 ViewsLast Modified:
I'm trying to update a stored procedure in SQL Server 2005 by adding another IF block to an existing structure.  


The original code is:

ALTER PROCEDURE [dbo].[TEST_dues_CalculateNonLBRDues] (@entity_id VARCHAR(10)
) AS

SET NOCOUNT ON
SET @entity_id = rtrim(ltrim(@entity_id))

DECLARE @dues_amount MONEY
DECLARE @number_of_divisions INT
DECLARE @division_cost INT

SET @dues_amount = 0
SET @number_of_divisions = 0
SET @division_cost = 9999


SELECT @number_of_divisions = ISNULL(entity_u1, 0) from entity where entity_id = @entity_id

SELECT @dues_amount = ISNULL(CAST(entity_u7 AS MONEY),0) FROM entity WHERE entity_id = @entity_id

IF ISNULL(@dues_amount,0) = 0
   BEGIN
      SELECT @dues_amount = dues_rate FROM duesbase
         INNER JOIN member ON member.cycle = duesbase.cycle
            AND member.mem_cat = duesbase.mem_cat
         WHERE entity_id = @entity_id
END

SET NOCOUNT OFF
SELECT @dues_amount


============================

I want to update the IF block to

IF ISNULL(@dues_amount,0) = 0
   BEGIN
      SELECT @dues_amount = dues_rate FROM duesbase
         INNER JOIN member ON member.cycle = duesbase.cycle
            AND member.mem_cat = duesbase.mem_cat
         WHERE entity_id = @entity_id
      IF  @number_of_divisions > 0
         BEGIN
              @dues_amount = @dues_amount + (@number_of_divisions * @division_cost)
         END
END

================================
but I can't get around the error,

Msg 102, Level 15, State 1, Procedure TEST_dues_CalculateNonLBRDues, Line 43
Incorrect syntax near '@dues_amount'.


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros