Adding Parameters to a function

[Ice Rhino]

I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(@vch_site_ref nvarchar (3), @dt_src_date datetime)

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, DiscEndDate datetime,

ErvTot float, Leaseterm int,

leasestart datetime, rentreview nvarchar(255),

leaseend datetime, breakclause datetime,

tenancyterm datetime, landact nvarchar(255),

datadate datetime)

AS

BEGIN

INSERT @WeeklyTerrierRSPI


SELECT Areacode, siteref, estatename, Securitised, unitref, unittype, unittype_count, tenantname,

tenantstatus, tenantstatus_count, unitstatus, unitstatus_count, floortotal, floortotocc,

initialvacarea, initialvacnet, TotalRent, NetRent, FinalRtLsincSC, DiscEndDate, ErvTot,

Leaseterm, leasestart, rentreview, leaseend, breakclause, tenancyterm, landact, datadate

FROM dbo.src_terrier

WHERE (datadate = @dt_src_date) AND (siteref = @vch_site_ref)


RETURN

END


I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;

Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16

Parameters were not supplied for the function 'fnWTRTerrierData'.

How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?

Regards

 

[pdx_man]

Hmmm works on my machine. This is the ALTER script. What is the CREATE script?

 

[giovi2002]

If you call a function within the procedure you should also specify the arguments for a parameterized function

I don't know by head how the function is being called within a SP but like in query analyzer all you need to do is to fill the values between the ( and )

I think an example would be something like this for a function with dateparameter and varchar parameter
execute fn_Myfunction('12-12-2006','Helloworld')

So specify the arguments to pass to the function, of course you can replace the arguments by declared parameters in the stored procedure

 

[Ice Rhino]

OK, noted and thanks