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

Error while trying to execute a user defined function

Hi,

I'm working on SQL SERVER 2005 Standard edition.

I created a user defined function on the dbo schema.

Is it possible to invoke a user defined function in a select clause only with the name of this function without precising the name of the schema.

if my function is called TOTO(), can i execute the command :

SELECT TOTO()

go

Actually when i try to execute this command i have the following error message :

Message 195, level 15, state 10 :

'TOTO' is not a known built-in function name option.

Can someone help me to solve this issue ?

try

select dbo.toto()

|||

I know that it works but i want to know if it's possible to execute this function without specifying the schema just like this :

SELECT toto()

go

|||

Locolito:

The response to your question is no: WIth a scalar function you must supply the "schema" qualifier -- such as dbo; however, the schema qualifier is not required with a table function.

Dave

Error while creating inline function - CREATE FUNCTION failed because a column name is not speci

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

|||

You have to specify an alias for the columns in the select statement. Change the query to somthing like this:

Code Snippet

SELECT ident_current('orders') AS 'ID'

I hope this answers your question.

Best regards,

Sami Samir

|||Thats easy:

Code Snippet

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders') AS Newidentity)

GO

Jens K. Suessmeyer.

http://www.sqlserver2005.de