How to execute function in sql with parameters - Solved

Execute function in SQL with parameters: In this tutorial, I am going to show with you how to execute a function in SQL with parameters. We will also talk about the single parameter as well as multiple parameters. 

Steps

1. Make a SQL Function with parameter

2. Call it With a select keyword

Let's See step by step process to execute SQL function with parameters. In the below function, I have created a function that will find the date of birth. You can make any function but the function calling process will be the same.

Also See: Use of Function at project Level

$ads={1}

Execute function in SQL with Single parameters

Create function age

(

@dob date

)

RETURNS int

AS

BEGIN

     declare @age int

     set @age=DATEDIFF(YEAR,@dob,GETDATE())-

     case

         when (month(@dob) > month(GETDATE())) or

               (month(@dob) = month(GETDATE()) and day(@dob) > day(GETDATE()))

         THEN 1

         ELSE 0

     END  

     RETURN @age

END

select dbo.age('01/02/2014')

select dbo.age('01/02/2014') as age

Execute function in SQL with Multiple parameters

In below function, I have a created function which is used to count record from a table, In this function, you will see the use of multiple parameters.

Calling of SQL function with multiple parameters

  select DBO.Login('Sana',123)

Create a table and Insert some Record then make a function. Definitely, You will be able to understand better.

CREATE TABLE [dbo].[Mytbl]

(

          [Name] [varchar](20) Primary Key Identity,

          [PhoneNo] [int] NULL,

          [Gender] [varchar](5) NULL,

          [Pass] [int] NULL,

          [Address] [varchar](20) NULL

) ON [PRIMARY]

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sana', 9125, 'F', 123, 'Noida')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sana', 9125, 'F', 123, 'Noida')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sanjay', 8989,'M', 0, 'Noida')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Manu', 8978, 'M', 0, 'Gzbd')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ram', 8958, 'M', 78945, 'Mayur Vihar')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sanjay Yadav', 121,'M', 0, 'noida')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ramesh ', 343, 'M', 23, 'AshokNagar')

INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ramesh ', 343, 'M', 23, 'AshokNagar')

  

Create a function with multiple parameters

Create function [dbo].[Login](@n varchar(20),@pwd int)

returns int

as

  begin

       declare @temp int

             select @temp=count(*) from Mytbl where Name=@n and Pass=@pwd

             return @temp

  end

  select DBO.Login('Sana',123)