• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

T-SQL Error

I have a small SQL Script and I keep getting an error when I try and execute/save the script.
Here is my script. I'm using SQL Server 2008. See the screenshot for the error message. Screenshot
USE [BlakesDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_RENTROLL_NRI_TOTALS]
	@DEAL_ID		FLOAT,
	@RENT_ROLL_ID	FLOAT

AS
BEGIN
	
	SELECT SUM(A.Rent_Amt) AS VACANT_AMT_RES
	  FROM PIPE_RENT_ROLL_DETAIL A 
		 INNER JOIN 
 	       PIPE_RENT_ROLL_STATUS_LKUP B ON A.RENT_ROLL_STATUS_ID = B.ID
	 WHERE A.DEAL_ID = @Deal_ID		         AND
	       A.RENT_ROLL_ID = @Rent_Roll_ID	AND
	       B.CATEGORY = 'VAC'			AND
	       A.TENANT_TYPE = 'RESIDENTIAL'
	
	IF VACANT_AMT_RES = NULL
		VACANT_AMT_RES = 0
	END IF	
END

Open in new window

0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to store the result into a variable:
ALTER PROCEDURE [dbo].[SP_RENTROLL_NRI_TOTALS]
	@DEAL_ID		FLOAT,
	@RENT_ROLL_ID	FLOAT

AS
BEGIN
DECLARE @VACANT_AMT_RES DECIMAL(20,4)	
	SELECT @VACANT_AMT_RES = SUM(A.Rent_Amt) 
	  FROM PIPE_RENT_ROLL_DETAIL A 
		 INNER JOIN 
 	       PIPE_RENT_ROLL_STATUS_LKUP B ON A.RENT_ROLL_STATUS_ID = B.ID
	 WHERE A.DEAL_ID = @Deal_ID		         AND
	       A.RENT_ROLL_ID = @Rent_Roll_ID	AND
	       B.CATEGORY = 'VAC'			AND
	       A.TENANT_TYPE = 'RESIDENTIAL'
	
	IF @VACANT_AMT_RES IS NULL
		SET @VACANT_AMT_RES = 0
	END IF	
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
resp:
ALTER PROCEDURE [dbo].[SP_RENTROLL_NRI_TOTALS]
	@DEAL_ID		FLOAT,
	@RENT_ROLL_ID	FLOAT

AS
BEGIN
	SELECT ISNULL(SUM(A.Rent_Amt),0)
	  FROM PIPE_RENT_ROLL_DETAIL A 
		 INNER JOIN 
 	       PIPE_RENT_ROLL_STATUS_LKUP B ON A.RENT_ROLL_STATUS_ID = B.ID
	 WHERE A.DEAL_ID = @Deal_ID		         AND
	       A.RENT_ROLL_ID = @Rent_Roll_ID	AND
	       B.CATEGORY = 'VAC'			AND
	       A.TENANT_TYPE = 'RESIDENTIAL'
	END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that FLOAT data types should not be used for ID fields.
FLOAT is a unprecise data type
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Ephraim WangoyaCommented:

What are you trying to do?

You can change the value of if it is a variable using set

      IF @VACANT_AMT_RES = NULL
            set @VACANT_AMT_RES = 0
            
0
 
BlakeMcKennaAuthor Commented:
Ok,

So far, none of the possible solutions have worked. For some reason, it doesn't like my IF statements. What I am trying to do is ultimately return 6 values to my Web App. Below is the result of trying to execute to save this proc.


Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 81
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 83
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 87
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 91
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 95
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 99
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure SP_RENTROLL_NRI_TOTALS, Line 107
Incorrect syntax near 'END'.

USE [BlakesDB]
GO
ALTER PROCEDURE [dbo].[SP_RENTROLL_NRI_TOTALS]
	@DEAL_ID		FLOAT,
	@RENT_ROLL_ID	FLOAT

AS
BEGIN
	DECLARE @MTHLY_NRI_AMT_RES		AS FLOAT
	DECLARE @ANNUAL_NRI_AMT_RES		AS FLOAT
	DECLARE @ACH_RR_YN_RES			AS CHAR
	
	DECLARE @MTHLY_NRI_AMT_COMM		AS FLOAT
	DECLARE @ANNUAL_NRI_AMT_COMM	AS FLOAT
	DECLARE @ACH_RR_YN_COMM			AS CHAR
	
	DECLARE @VACANT_AMT_RES			AS FLOAT
	DECLARE @CONCESSION_AMT_RES		AS FLOAT
	DECLARE @RENT_AMT_RES			AS FLOAT
	DECLARE @VACANT_AMT_COMM		AS FLOAT
	DECLARE @CONCESSION_AMT_COMM	AS FLOAT
	DECLARE @RENT_AMT_COMM			AS FLOAT
	
--This sections retrieves the totals for RESIDENTIAL	
	SELECT @VACANT_AMT_RES = SUM(A.Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
			 INNER JOIN 
		   PIPE_RENT_ROLL_STATUS_LKUP B ON A.RENT_ROLL_STATUS_ID = B.ID
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   B.CATEGORY = 'VAC'				AND
		   A.TENANT_TYPE = 'RESIDENTIAL'
	
	
	SELECT @CONCESSION_AMT_RES = SUM(A.UW_Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   A.TENANT_TYPE = 'RESIDENTIAL'
	
	
	SELECT @RENT_AMT_RES = SUM(A.Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   A.TENANT_TYPE = 'RESIDENTIAL'


--This section retrieves the totals for COMMERCIAL
	SELECT @VACANT_AMT_COMM = SUM(A.Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
			 INNER JOIN 
		   PIPE_RENT_ROLL_STATUS_LKUP B ON A.RENT_ROLL_STATUS_ID = B.ID
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   B.CATEGORY = 'VAC'				AND
		   A.TENANT_TYPE = 'COMMERCIAL'
	
	
	SELECT @CONCESSION_AMT_COMM = SUM(A.UW_Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   A.TENANT_TYPE = 'COMMERCIAL'
	
	
	SELECT @RENT_AMT_COMM = SUM(A.Rent_Amt)
	  FROM PIPE_RENT_ROLL_DETAIL A 
	 WHERE A.DEAL_ID = @Deal_ID				AND
		   A.RENT_ROLL_ID = @Rent_Roll_ID	AND
		   A.TENANT_TYPE = 'COMMERCIAL'


	IF @VACANT_AMT_RES IS NULL
		SET @VACANT_AMT_RES = 0
	END IF
			
	IF @RENT_AMT_RES IS NULL
		SET @RENT_AMT_RES = 0
	END IF
			
	IF @CONCESSION_AMT_RES IS NULL
		SET @CONCESSION_AMT_RES = 0
	END IF
	
	IF @VACANT_AMT_COMM IS NULL
		SET @VACANT_AMT_COMM = 0
	END IF
			
	IF @RENT_AMT_COMM IS NULL
		SET @RENT_AMT_COMM = 0
	END IF
			
	IF @CONCESSION_AMT_COMM IS NULL
		SET @CONCESSION_AMT_COMM = 0
	END IF		
		
	SET @ANNUAL_NRI_AMT_RES = @RENT_AMT_RES - @VACANT_AMT_RES - @CONCESSION_AMT_RES
	SET @ANNUAL_NRI_AMT_COMM = @RENT_AMT_COMM - @VACANT_AMT_COMM - @CONCESSION_AMT_COMM
	
	PRINT @ANNUAL_NRI_AMT_RES
	PRINT @ANNUAL_NRI_AMT_COMM
	
END

Open in new window

0
 
Ephraim WangoyaCommented:

Remove the ENDIF

        IF @VACANT_AMT_RES IS NULL
            SET @VACANT_AMT_RES = 0

                  
      IF @RENT_AMT_RES IS NULL
            SET @RENT_AMT_RES = 0

        ...............
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now