Inline table valued function in sql (User Defined )

Inline table valued function in sql|User Defined Functions|Table Valued Functions|

Table Scripts:

create table Country
(
CountryId int primary key,
Name varchar(100)
)

insert into Country values(1,'India')
insert into Country values(2,'UK')
insert into Country values(3,'USA')


create Table Customer
(
CustId int,
Name Varchar(100),
DOB date,
CountryId int foreign key references Country(CountryId)
)

Insert into Customer values(1,'Arun','1995-08-21',1)
Insert into Customer values(2,'Sara','2001-12-05',2)
Insert into Customer values(3,'Haris','1991-03-30',1)
Insert into Customer values(4,'Peter','1998-06-15',null)
Insert into Customer values(5,'Kumar','1985-11-07',1)


--drop table Country
--drop table Customer

--Inline Table Value function
--returns table data
--Not included begin end statement


select * from Country
select * from Customer

--Create function of inline table valued function

create function udf_loadCustomer
(
@countryid int
)
returns table
as

return
(
select CustId as CustomerId,Name as CustomerName,DOB,CountryId
from Customer where countryid=@countryid
)


--passing parameter to function
select * from dbo.udf_loadCustomer(1)

--using where condition

select * from dbo.udf_loadCustomer(1) where CustomerId =3

--useful for join

select * from dbo.udf_loadCustomer(1) A join country B on A.CountryId=B.CountryId

---Default value passing for getting all result
alter function udf_loadCustomer
(
@countryid int=0
)
returns table
as
return
(
select CustId as CustomerId,Name as CustomerName,DOB,CountryId
from Customer where countryid=@countryid or @countryid=0
)


select * from dbo.udf_loadCustomer(0) A left join country B on A.CountryId=B.CountryId


--drop function udf_loadCustomer

Regards,
Sql Query for jobs
#https://www.youtube.com/@SqlQueryforjobs

#sql #mysql #thisisqld #sqlserver #sqlab #nosql #postgresql #whitsundaysqld #jsuisqlf #northlakesqld #pacosqlos #redlandsqld #esql #gatosqls #sqlinjection #sqlimer #pacosqls #mssql #southerndownsqld #igersqld #sqldeveloper #plsql #batsqld #sqlite #nswvsqld #cmcrocksqld #nationalparksqld #pacosql #bestplacesqld #sqlsaturday #holtyspicsqld #cairnsqld #sqlmiami #mysqli #phpmysql #unicefmasqla #sqlife #smallbusinessqld #personalisedplatesqld #oldsql
#sql #python #java #javascript #programming #html #database #sqlserver #css #php #coding #developer #programmer #software #datascience #mysql #o #webdeveloper #machinelearning #code #audiomobil #sqldeveloper #spl #computerscience #sq #oracle #webdevelopment #data #dataanalytics #linux
#technology #computer #development #bigdata #backend #programador #coder #codinglife #caraudio #microsoft #csharp #sqldatabase #softwaredeveloper #informationtechnology #tecnologia #mongodb #powerbi #developers #c #it #worldcode #ti #web #programminglife #softwaredevelopment #artificialintelligence #ai #cienciadacomputacao #angular #programmerslife
stored procedures,stored procedures sql,stored procedure,sql stored procedures,what is a stored procedure,stored proc sql,how to use a stored procedure,stored proc,sql server,sql stored proc,alex the analyst,data analyst career,data analyst,data analytics,database
stored procedure in sql server,
create a stored procedure in sql server management studio,
stored procedure in sql server javatpoint,
stored procedure in sql server optimization,
stored procedure in sql server practice,
stored procedure in sql server syntax,
Difference between Stored procedures and Functions