• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

SQL Server Named Parameters

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

Kevin
0
zorvek (Kevin Jones)
Asked:
zorvek (Kevin Jones)
1 Solution
 
David KrollCommented:
sure.

ex:

CREATE FUNCTION [dbo].[function1]   ( @pvar1 varchar(30),  @pvar2 varchar(30), @pvar3 int )
0
 
Shaun KlineLead Software EngineerCommented:
Assuming you mean user defined functions, yes.
http://www.sqlteam.com/article/user-defined-functions
0
 
cyberkiwiCommented:
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)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
cyberkiwiCommented:
Welcome to SQL, Kevin.
0
 
cyberkiwiCommented:
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

0
 
zorvek (Kevin Jones)ConsultantAuthor Commented:
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
0
 
cyberkiwiCommented:
Yes. Defaults are not available to functions.
Best bet to implement "defaults" is to have functions that call other functions.
(here add2 is a shorter form of add3 with param3 defaulted to 0)

create function add2(@a int, @b int) returns int as
begin
return dbo.add3(@a, @b, 0)
end
GO
0
 
zorvek (Kevin Jones)ConsultantAuthor Commented:
cyberkiwi,

Kewl!

Sort of like overloading. I like.

Thanks!

Kevin
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now