Problem with Functions and Procedures
-
Apologies if this is in the wrong section/heading
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
-
The first error is because your language setting require dates in mm/dd/yyyy format.
The second error will be caused by a zero value divisor. Check the values of floortotal, FinalRtLsincSC and floortotocc. One of these will be zero and if a valid value then protect the divide with a CASE statement.
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you for that. I will look at the settings for the date, you are right with your second point too. There are some zero's in the mix. I shall now go and investigate the CASE statement to which you refer. Any pointers or reference sites you can think of would be useful
Thanks for your reponse. Get better response on here on a Sunday than I do mon to Fri on technical support lines.
-
Look in BOL (Books Online) it will give you what you need eg
for a / b
CASE WHEN b=0 THEN 0 ELSE a / b END
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for that. I looked it up in my Transact-SQL book and it gave me an idea how I can do it. Thanks for the direction to go in
Regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply