asked on

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.

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

Guy Hengel [angelIII / a3]

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

Guy Hengel [angelIII / a3]

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

Guy Hengel [angelIII / a3]

note that FLOAT data types should not be used for ID fields.
FLOAT is a unprecise data type

Ephraim Wangoya

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

BlakeMcKenna

ASKER

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

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial