December 24, 2004 at 4:33 am
Hi
I have a SQL function say fn_A(s) that takes a parameter say s.
I want to add another parameter to this function and give a default value, so that , if at any other place in DB this function is called, it need not pass the second parameter.
When i modified the function and altered another sp from where this function fn_A(s) is called i got an error saying "Incorrect parameters are passed to this function". Does in mean that a function cannot have last parameter as defualt parameter like how we can do for stored procedures.
My requirement is to pass a second parameter to this function without affecting the rest of the calls. Is it possible any other way?
The easiest way would be converting this function into a sp, but i dont want to do that.
Help !!!
December 24, 2004 at 2:43 pm
Unfortunately, Books On Line says that you can use default parameters, but you have to specify the keyword "default". Yuck!
Here is an example that worked for me, but it's not going to solve your problem:
create function dbo.fn_test (@test varchar(10) = 'test' )
returns varchar(10)as
begin
return @test-2
end
go
select dbo.fn_test(default)
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 24, 2004 at 2:54 pm
This is what I would probably do. Say you are using your existing function in hundreds of places, but you have a need for the second parameter in 10 places. So, instead of adding the default parameter to the function, why don't you just create a second function for those 10 exceptions?
Here is an example:
create function dbo.fn_test (@test varchar(10))
returns varchar(11)
as
--This is your existing function used in hundreds of places in your code
begin
return @test-2 + '!'
end
go
create function dbo.fn_test2(@test varchar(10), @test2 varchar(10) = 'test')
returns varchar(21)
as
--this is the new function, it calls the first function
begin
set @test-2 = dbo.fn_test(@test)
return @test-2 + @test2
end
go
select dbo.fn_test2('abc','def')
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 28, 2004 at 12:04 am
Hi,
Thanks for the suggestion
I ll try it out
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply