Error with 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

hi,

i hope u shold try the date format as '2006-06-01' or in case u want to use your own date format which u are using

try using

@.dt_src_date = N'28/04/2006'

try convert(varchar,@.dt_src_date,103) = N'28/04/2006'

hope should work

regards

www.snktheone.com

|||

I will give that a go

Thanks