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. 

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.

Also See: Use of Function at project Level

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)

Read More

Important Sql complex queries interview questions

Introduction: In this tutorial, I am going to explain the SQL complex queries which is asked in every interview questions. So let's start today's tutorial. So let's start SQL complex queries fo practice with answers.

1. To add new 

Alter table employee add gender varchar(5)

2.Drop a column

Alter table employee drop column gender

                       Some Ms. Sql commands with examples

3.Or operator  in SQL

SELECT * from employee where employeeid=2 or employeeid=1

4.IN function in SQL 

SELECT * from employee where employeeid in(2,3)

5. Find record Not in SQL table using  IN function

SELECT * from employee where employeeid not in (1,2)

6.Between function in SQL

SELECT * from employee where employeeid between 1 and 2

7.Select commands with order by clause

SELECT * from employee order by name 

SELECT * from employee order by name desc

SELECT * from employee order by name asc

8.Max Function

SELECT max(name) from employee

SELECT max(salary) from employee

9.Max Function

SELECT min(salary) from employee

SELECT min(name) from employee

10.Avg Function

SELECT AVG(salary) from emp

11.LEFT Function

SELECT LEFT('Manish',3)

Result:Man

12.RIGHT Function

SELECT RIGHT('Manish',3)

Result:ish

13.CHARINDEX Function- Is use to find the index(position) of any character

SELECT CHARINDEX('i','Manish')

Result:4

14.LEN Function- Is use to find the length of character

SELECT LEN('Manish')

Result:6

15.GETDATE ()

Returns a datetime data type which contains current system data and time: 2018-09-24 09:51:36.568.

SELECT GETDATE()

16.Use of AND Operator

SELECT * from employee where employeeid=2 and employeeid=5

SELECT * from employee where employeeid=2 and salary=600

17.Use of OR Operator

SELECT * from employee where employeeid=2 or employeeid=5

SELECT * from employee where employeeid=2 or salary=4000

18.Use of IN Operator

SELECT * from employee where employeeid in (2,5,8)

19.Use of Between Operator

SELECT * from employee where employeeid between 3 and 7

20.Use of WildCard Operator

SELECT * from employee where name not like 'a%'

SELECT * from employee where name like '%a'

SELECT * from employee where name like '%m%'

SELECT * from employee where name like '_a%'

SELECT * from employee where name like '%a_'

SELECT * from employee where name like '[a,b]%'

SELECT * from employee where name like '[a-p]%'

21.Use of SUBSTRING() Function

SELECT SUBSTRING('Mohan',1,3)

Result:Moh

Read More