Temporary Function Parameters
-
I have written some functions and want to test them but as soon as I execute the function I get the following;
Msg 216, Level 16, State 1, Line 1
Parameters were not supplied for the function 'dbo.fnWTRCenPrtDivData'.
I understand that I need to pass the parameters into the function in order to run it, but When I press Ctrl+Sht+M I get the Specify Value for Templete Parameters box come up with Parameter, Type and Value, but the values are blank and there is no option to add the temprary params that I wish to test with.
Anybody shed some light on this?
-
typically you get the results of a function like this:
select dbo.fnWTRCenPrtDivData(param1,param2)
if any of the parameters are supposedly optional, where they assume a value, you need to use the default keyword:
select dbo.fnWTRCenPrtDivData(param1,default)
select dbo.fnWTRCenPrtDivData(default,default)
HTH
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
create function ufn_test(
@name1 varchar(30)='Walmart',
@name2 varchar(30)='cookies')
returns varchar(255)
BEGIN
return 'I go to ' + @name1 + ' to buy ' + @name2
END
GO
SET NOCOUNT ON
select dbo.ufn_test (default,default)
select dbo.ufn_test ('Sears',default)
select dbo.ufn_test ('Sears','wrenches')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you, I will give that a run. I appreciate you looking at this for me.
Regards
-
This is getting on my nerves now. Here is the full monty and for the life of me I can not understand why it will not work.
Function 1:-
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnWTRCenPrtDivData]
(
@chr_div char(2)='NE', @vch_portfolio_no tinyint='1')
RETURNS
@WeeklyTerrierRSPII TABLE
(
pormgr varchar(50), divmgr varchar(50),siteref Varchar(3), div char(2), propcat nvarchar(4))
AS
BEGIN
INSERT
@WeeklyTerrierRSPII
SELECT
src_portfolio_mgr.portfolio_mgr AS pormgr, src_div_mgr.div_mgr AS divmgr,
src_centre_list
.Site_Ref AS siteref, src_div_mgr.division AS div, src_centre_list.propcat
FROM
src_centre_list 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
RETURN
END
When Executed Command Completed Successfully
Function 2:-
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
ALTER
FUNCTION [dbo].[fnWTRTerrierData]
(
@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)
RETURN
END
When Executed Command Completed Successfully
Function 3:-
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnWTRReport]
(
@dt_src_date datetime, @chr_div char(2), @vch_portfolio_no tinyint, @nvch_propcat nvarchar (4))
RETURNS
@WeeklyTerrierRSPIII TABLE
(
Areacode varchar(2), 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, pormgr varchar(50), divmgr varchar(50),siteref Varchar(3), div char(2), propcat nvarchar(4))
AS
BEGIN
INSERT @WeeklyTerrierRSPIII
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, fnWTRCenPrtDivData.pormgr,
fnWTRCenPrtDivData
.divmgr, fnWTRCenPrtDivData.div, fnWTRCenPrtDivData.propcat
FROM
dbo.fnWTRCenPrtDivData INNER JOIN
dbo
.fnWTRTerrierData ON fnWTRCenPrtDivData.siteref = fnWTRTerrierData.siteref
RETURN
END
GO
When executed I get
Msg 216, Level 16, State 1, Procedure fnWTRReport, Line 20
Parameters were not supplied for the function 'dbo.fnWTRCenPrtDivData'.
Why for the love of all things lovely in the world do I get this error every time.
Anybody please help me!!!
-
the problem is your FROM statements in function 3:
FROM dbo.fnWTRCenPrtDivData INNER JOIN
dbo
.fnWTRTerrierData ON fnWTRCenPrtDivData.siteref = fnWTRTerrierData.siteref
nowhere are you using the required parameters for the functions; i think it should look something more like this:
FROM (SELECT * FROM dbo.fnWTRCenPrtDivData(DEFAULT,DEFAULT) AS fnWTRCenPrtDivData INNER JOIN
(SELECT * FROM dbo
.fnWTRTerrierData('05/04/2006') ) AS fnWTRTerrierData ON fnWTRCenPrtDivData.siteref = fnWTRTerrierData.siteref
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
if that is your suggestion then that is the re-write I shall do. Thanks for pointing me in the right direction.
Thank you for your help, much appreciated
Regards,
-
it also looks like the variables required are available in the third function; in that case it might look more like:
FROM (SELECT * FROM dbo.fnWTRCenPrtDivData(@chr_div, @vch_portfolio_no ) AS fnWTRCenPrtDivData INNER JOIN
(SELECT * FROM dbo
.fnWTRTerrierData(@dt_src_date) ) AS fnWTRTerrierData ON fnWTRCenPrtDivData.siteref = fnWTRTerrierData.siteref
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I believe that you don't need the subselects. Table functions can be used just like any other table so the following syntax will also work:
FROM dbo.fnWTRCenPrtDivData(@chr_div, @vch_portfolio_no ) AS fnWTRCenPrtDivData
INNER JOIN dbo.fnWTRTerrierData(@dt_src_date) AS fnWTRTerrierData
ON fnWTRCenPrtDivData.siteref = fnWTRTerrierData.siteref
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply