December 21, 2015 at 6:17 am
I want to have Multiple parameter function like concat :
MY_FUN( string1, string2, ... string_n )
whats syntax look like?
December 21, 2015 at 6:22 am
Will be exactly how you call the CONCAT function and how you have already discribed it
CREATE FUNCTION dbo.my_func (@string1 varchar(10), @string2 varchar(10),............, @stringn varchar(10)
...
...
...
dbo.my_func ('string1','string2',.....,'stringn')
December 21, 2015 at 6:24 am
https://msdn.microsoft.com/en-GB/library/ms186755.aspx
--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
--Transact-SQL Inline Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
December 21, 2015 at 6:25 am
If you mean a function that takes an undisclosed number of parameters, you can't create such a function. You have to define the parameters exactly when the function is created and specify them all when the function is called (I don't think that functions have optional parameters that can be left out of the call)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2015 at 6:42 am
If your goal is to pass a list of values to your function and then treat it as a table to perform a query, you could pass a delimited list in a single parameter, then split it and use it as a table in the function. See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for a good string splitter ITVF that returns a table.
If each string has a different purpose, then I think you're out of luck.
December 21, 2015 at 6:57 am
GilaMonster (12/21/2015)
I don't think that functions have optional parameters that can be left out of the call
That's correct, neither optional parameters nor default values can be defined for User Defined Functions.
😎
Note that default value parameter syntax doesn't cause syntax error which can be misleading as it does not work.
December 21, 2015 at 7:18 am
Ed Wagner (12/21/2015)
If your goal is to pass a list of values to your function and then treat it as a table to perform a query, you could pass a delimited list in a single parameter, then split it and use it as a table in the function. See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for a good string splitter ITVF that returns a table.If each string has a different purpose, then I think you're out of luck.
Or pass a table valued parameter instead of a delimited list of values you have to parse. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 1, 2016 at 9:06 am
Eirikur Eiriksson (12/21/2015)
GilaMonster (12/21/2015)
I don't think that functions have optional parameters that can be left out of the callThat's correct, neither optional parameters nor default values can be defined for User Defined Functions.
😎
Note that default value parameter syntax doesn't cause syntax error which can be misleading as it does not work.
Agree that UDFs do not support optional parameters that can be left out of the call which is why the OP cannot implement their idea however UDFs support default values as far as how I utilize them. What did you mean by "does not work"?
use tempdb;
go
if object_id(N'dbo.test_default_parameters') is not null
drop function dbo.test_default_parameters;
go
create function dbo.test_default_parameters(@one int,
@two int = 0 -- default value
)
returns table
as
return (select @one as one, @two as two);
go
select *
from dbo.test_default_parameters(1,default); -- default keyword
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 1, 2016 at 9:10 am
Sean Lange (12/21/2015)
Ed Wagner (12/21/2015)
If your goal is to pass a list of values to your function and then treat it as a table to perform a query, you could pass a delimited list in a single parameter, then split it and use it as a table in the function. See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for a good string splitter ITVF that returns a table.If each string has a different purpose, then I think you're out of luck.
Or pass a table valued parameter instead of a delimited list of values you have to parse. 🙂
+1
There are some functional use cases where splitting strings is still necessary, e.g. when asking SSRS to send the value of a multi-select parameter to a stored procedure input parameter, but in general the need to pass delimited strings into a database API has been greatly reduced since TVPs were introduced in 2008.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 2, 2016 at 4:37 am
Orlando Colamatteo (1/1/2016)
Eirikur Eiriksson (12/21/2015)
GilaMonster (12/21/2015)
I don't think that functions have optional parameters that can be left out of the callThat's correct, neither optional parameters nor default values can be defined for User Defined Functions.
😎
Note that default value parameter syntax doesn't cause syntax error which can be misleading as it does not work.
Agree that UDFs do not support optional parameters that can be left out of the call which is why the OP cannot implement their idea however UDFs support default values as far as how I utilize them. What did you mean by "does not work"?
use tempdb;go
if object_id(N'dbo.test_default_parameters') is not null
drop function dbo.test_default_parameters;
go
create function dbo.test_default_parameters(@one int,
@two int = 0 -- default value
)
returns table
as
return (select @one as one, @two as two);
go
select *
from dbo.test_default_parameters(1,default); -- default keyword
go
Thanks for the correction Orlando, didn't word this properly. With "does not work" I meant as a definition of an optional parameter, that is as optional parameter definition work in a stored procedure.
😎
January 2, 2016 at 11:58 am
.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2016 at 2:01 pm
Eirikur Eiriksson (1/2/2016)
Orlando Colamatteo (1/1/2016)
Eirikur Eiriksson (12/21/2015)
GilaMonster (12/21/2015)
I don't think that functions have optional parameters that can be left out of the callThat's correct, neither optional parameters nor default values can be defined for User Defined Functions.
😎
Note that default value parameter syntax doesn't cause syntax error which can be misleading as it does not work.
Agree that UDFs do not support optional parameters that can be left out of the call which is why the OP cannot implement their idea however UDFs support default values as far as how I utilize them. What did you mean by "does not work"?
use tempdb;go
if object_id(N'dbo.test_default_parameters') is not null
drop function dbo.test_default_parameters;
go
create function dbo.test_default_parameters(@one int,
@two int = 0 -- default value
)
returns table
as
return (select @one as one, @two as two);
go
select *
from dbo.test_default_parameters(1,default); -- default keyword
go
Thanks for the correction Orlando, didn't word this properly. With "does not work" I meant as a definition of an optional parameter, that is as optional parameter definition work in a stored procedure.
😎
Figured you knew about DEFAULT so was wondering if you knew something I was missing about the API options. Thanks for confirming.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply