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 you can execute the function of SQL with parameter. We will also talk about the single parameter as well as multiple parameters. 

execute function in sql with multiple parameters


Steps

1. 
Make a SQL Function with parameter
2. Call it With select keyword

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




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)


Newest
Previous
Next Post »