Thursday, June 24, 2010

Sunny Mehra's Finding's on User defined function for default values in SQL Server 2005

I had done a change in a User defined function and added a new parameter.
I wanted this parameter to have a default value
Create Function
Id int,
Name varchar(20)=null ----newly added
RETURNS @RetCSList TABLE (Id int NOT NULL, xyz int)
However when I was trying to call this function in another Sp by following code but it was not allowed.
Select * from abc(1)
I thought that the default null value would be automatically picked up by function during its call as is the case while executing SP.
However that is not allowed in case of UDF
So for UDF correct code would be :
Select * from abc(1, DEFAULT) ----DEFAULT is a keyword and signals function to pick default value.
Or pass a value explictly like
Select * from abc(1, ' ' )
Happy Reading
Sunny Mehra

No comments:

Recent Posts