Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)Flag for United States of America asked on

SQL Server Named Parameters

Can named parameters be used in a function call as they can be in a stored procedure call?

Kevin
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
zorvek (Kevin Jones)

8/22/2022 - Mon
David Kroll

sure.

ex:

CREATE FUNCTION [dbo].[function1]   ( @pvar1 varchar(30),  @pvar2 varchar(30), @pvar3 int )
Shaun Kline

Assuming you mean user defined functions, yes.
http://www.sqlteam.com/article/user-defined-functions
cyberkiwi

NO

create function dbo.namedfun (@i int , @j int) returns int
as begin
return @i+@j
end

select dbo.namedfun (1, 2)
select dbo.namedfun (@i=1, @j=2)
Your help has saved me hundreds of hours of internet surfing.
fblack61
cyberkiwi

Welcome to SQL, Kevin.
cyberkiwi

A more complete sample
One line will fail as indicated
use tempdb
GO
create function dbo.namedfun (@i int , @j int) returns int
as begin
return @i+@j
end
GO
select dbo.namedfun (1, 2) -- ok
GO
select dbo.namedfun (@i=1, @j=2) -- fail
GO
drop function namedfun
GO
create proc dbo.namedproc @i int , @j int
as
select @i * 10 + @j
GO
exec dbo.namedproc 1, 2 -- ok, result is 12
GO
exec dbo.namedproc @j=1, @i=2 -- ok, result is 21, not 12
GO
drop proc namedproc
GO

Open in new window

ASKER
zorvek (Kevin Jones)

So we can't call a function with named arguments like we can with a stored procedure. And, from what I have learned since posting the question, ALL the parameters have to ALWAYS be passed.

Correct?

Kevin
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
cyberkiwi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
zorvek (Kevin Jones)

cyberkiwi,

Kewl!

Sort of like overloading. I like.

Thanks!

Kevin