Passing Default Value to A parameter in Function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Passing Default Value to A parameter in Function


Hi I am trying to create a new function in SQL Server 2005 Enterprise edision
and passing two parameter
Create Function f(
@Type INT = 0,
@TargetDate datetime = NULL)
RETURNS @result table
(
i int,
Name Varchar(20)
) When i run this funstion Like this SELECT * FROM f It gives me error (Parameters were not supplied for the function) Ane halp
Thank You
Is that your function definition or you didnt post the code here? Were you able to create the function without any errors? ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
Create Function f(
@Type INT = 0,
@TargetDate datetime = NULL)
RETURNS @result table
(
i int,
Name Varchar(20)
)
AS
BEGIN
SELECT ID,Name FROM tblAgent
WHERE AgentType = @Type
AND ApplicationDate = @TargetDate
END
did you compile the function without errors? ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
Yes
I dont think there is any version of SQL Server that will compile the code you have as-is.
(1) You cannot return data via a SELECT statement like you have in the function
(2) There is no RETURN statement. You might want to convert this to a stored proc instead. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
<< SELECT * FROM f >> You need to pass values to function’s parameters Madhivanan Failing to plan is Planning to fail
Create Function f(
@Type INT = 0,
@TargetDate datetime = NULL)
RETURNS @result table
(
i int,
Name Varchar(20)
)
AS
BEGIN
INSERT INTO @Result
SELECT ID,Name FROM tblAgent
WHERE AgentType = @Type
AND ApplicationDate = @TargetDate
RETURN
END So if i use this function like this
SELECT * FROM f
then both parameter values should be default value
@Type INT = 0
AND @TargetDate datetime = NULL But it gives me this error Parameters were not supplied for the function Any help
Thank You

When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. select * from f (default,default)
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Thanks
It worked
For most of our issues BOL is the best solution because researching anywhere else [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
Also BOL is Books On Line, sql server help file [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
]]>