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
Post your Stored Procedure codes.|||SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spWTRWeeklyTerrierData]
(@.vch_site_ref nvarchar(3), @.dt_src_date datetime)
AS
BEGIN
SET NOCOUNT ON;
SELECT fnWTRTerrierData.Areacode, fnWTRTerrierData.siteref, fnWTRTerrierData.estatename, fnWTRTerrierData.Securitised, fnWTRTerrierData.unitref, fnWTRTerrierData.unittype, fnWTRTerrierData.unittype_count, fnWTRTerrierData.tenantname,fnWTRTerrierData.tenantstatus, fnWTRTerrierData.tenantstatus_count, fnWTRTerrierData.unitstatus, fnWTRTerrierData.unitstatus_count, fnWTRTerrierData.floortotal, fnWTRTerrierData.floortotocc, fnWTRTerrierData.initialvacarea, fnWTRTerrierData.initialvacnet, fnWTRTerrierData.TotalRent, fnWTRTerrierData.NetRent, fnWTRTerrierData.FinalRtLsincSC, fnWTRTerrierData.ErvTot, fnWTRTerrierData.tenancyterm, fnWTRTerrierData.landact, fnWTRTerrierData.datadate
FROM fnWTRTerrierData
RETURN
END
|||Looking at a stored procedure I pulled over from a SQL 2000 DB, I took out the elements that seems to resemble the function side
BEGIN
SET NOCOUNT ON;
RETURN
END
But I am still met with the same parameters message when I try to execute the SQL code to create the SP.
Regards
|||
ALTER FUNCTION [dbo].[fnWTRTerrierData]
(
@.vch_site_ref nvarchar (3),
@.dt_src_date datetime
)
Your table function is expecting 2 input parameter which you did not supply.
You will need to supply these 2 value to the function
for example
SELECT ...
FROM fnWTRTerrierData ('?', '2006-01-01')
