Error on Function and Procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error on Function and Procedures

I have just streamlined my pile of functions and reloaded the result into a Stored Procedure. I now have two different errors. here are the two FN’s and the SP. This will be a long message so apologise for its length; Function 1:-
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnWTRalldata] (@dt_src_date datetime,@chr_div char(2), @vch_portfolio_no tinyint,@vch_prop_cat nvarchar(4)) RETURNS @WeeklyTerrierRSPI TABLE (Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4)) AS BEGIN INSERT @WeeklyTerrierRSPI SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat FROM src_terrier INNER JOIN src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no WHERE (src_terrier.datadate = @dt_src_date) AND (src_terrier.Areacode = @chr_div) AND ( src_centre_list.Portfolio_no = @vch_portfolio_no) AND( src_centre_list.propcat = @vch_prop_cat) RETURN END GO Function 2:-
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnWTRalldataReport] (@dt_src_date datetime,@chr_div char(2), @vch_portfolio_no tinyint,@vch_prop_cat nvarchar(4)) RETURNS @WeeklyTerrierRSPII TABLE (Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,floorspaceperc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float,rentrolldiscperc float,netrentpersqft float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4)) AS BEGIN INSERT @WeeklyTerrierRSPII SELECT fnWTRalldata.Areacode, fnWTRalldata.siteref, fnWTRalldata.estatename, fnWTRalldata.Securitised, fnWTRalldata.unitref, fnWTRalldata.unittype, fnWTRalldata.unittype_count, fnWTRalldata.tenantname, fnWTRalldata.tenantstatus, fnWTRalldata.tenantstatus_count, fnWTRalldata.unitstatus, fnWTRalldata.unitstatus_count, fnWTRalldata.floortotal, fnWTRalldata.floortotocc, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, fnWTRalldata.initialvacarea, fnWTRalldata.initialvacnet, fnWTRalldata.TotalRent, fnWTRalldata.NetRent, fnWTRalldata.FinalRtLsincSC,(fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) – 1 AS rentrolldiscperc, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, fnWTRalldata.ErvTot, fnWTRalldata.tenancyterm, fnWTRalldata.landact, fnWTRalldata.datadate, fnWTRalldata.div_mgr, fnWTRalldata.portfolio_mgr, fnWTRalldata.propcat FROM dbo.fnWTRalldata (@dt_src_date, @chr_div , @vch_portfolio_no, @vch_prop_cat)
RETURN END GO STORED PROCEDURE :- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spWTRalldatareportsummary] (@dt_src_date datetime,@chr_div char(2), @vch_portfolio_no tinyint,@vch_prop_cat nvarchar(4)) AS BEGIN –SET NOCOUNT ON; SELECT Areacode,siteref,estatename, Securitised,unitref,unittype,unittype_count, tenantname,tenantstatus, tenantstatus_count,unitstatus, unitstatus_count,floortotal,floortotocc,floorspaceperc,initialvacarea, initialvacnet,TotalRent,NetRent,FinalRtLsincSC,rentrolldiscperc,netrentpersqft, ErvTot, tenancyterm, landact,datadate,div_mgr,portfolio_mgr,propcat FROM fnWTRalldataReport (@dt_src_date, @chr_div , @vch_portfolio_no, @vch_prop_cat) END GO The Problem I have is two fold. When I execure the procedure and run USE [DashboardSQL-2K5] GO DECLARE @return_value int EXEC @return_value = [dbo].[spWTRalldatareportsummary] @dt_src_date = N’28/04/2006′, @chr_div = N’SW’, @vch_portfolio_no = 4, @vch_prop_cat = N’core’ SELECT ‘Return Value’ = @return_value GO if I put the date in as 28/04/2006 I get an error like:- Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0 Error converting data type nvarchar to datetime. (1 row(s) affected) If I put the date in as 04/28/2006 I get an error like :- Msg 8134, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 18 Divide by zero error encountered. The statement has been terminated. (1 row(s) affected) Could anyone help me on this problem please as my whole project is now being help up by something stupid I have done. Thanks in advance
Toni Chaffin
aka Toni
Check to make sure fnWTRalldata.floortotal , fnWTRalldata.FinalRtLsincSC and fnWTRalldata.floortotocc are never zero before using them as denominators in your fnWTRalldataReport function. You may add CASE statements in the function to help check for the zero values. e.g for the floorspaceperc column:
…….,floorspaceperc = CASE WHEN fnWTRalldata.floortotal>0 THEN fnWTRalldata.floortotocc/fnWTRalldata.floortotal ELSE 0 END, …. Nathan H. Omukwenyi
MVP [SQL Server]
To solve the first error try next code:
DECLARE @return_value int
declare @sourceDate dateTime set @sourceDate = ‘20060428’ EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = @sourceDate,
@chr_div = N’SW’,
@vch_portfolio_no = 4,
@vch_prop_cat = N’core’
I shall try that, thank you Regards
Toni Chaffin
aka Toni
for the first error, you’re using the default format of mm/dd/yyyy for the date. in the where clause, use the CONVERT function with specific style for the format of your choosing. for the second error, use nathan suggestion. Consider also when the value is null (you may want to use the case isnull(fnWTRalldata.floortotal,0) = 0 then 0 else fnWTRalldata.floortotocc/fnWTRalldata.floortotal end. May the Almighty God bless us all!
www.empoweredinformation.com
]]>