BlakeMcKenna
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.
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
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
note that FLOAT data types should not be used for ID fields.
FLOAT is a unprecise data type
FLOAT is a unprecise data type
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
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'.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window