How can I avoid repeating the same query?-CodePudding

Hi I have a query in below. I use this query in multiple place but this queries almost same. Only where conditions change.

This is my query

select
c.first_name,
c.middle_name,
c.last_name,
c.birth_day,
c.birth_month,
c.birth_year,
c.marital_status,
c.education_status,
c.departmant,
c.create_date,
c.create_user,
c.modify_date,
c.modify_user
from customer c 
where c.place='london' and c.create_date>getdate()-100

I use same query in multiple place but only where conditions change. So I repeat below part everywhere.

select
c.first_name,
c.middle_name,
c.last_name,
c.birth_day,
c.birth_month,
c.birth_year,
c.marital_status,
c.education_status,
c.departmant,
c.create_date,
c.create_user,
c.modify_date,
c.modify_user
from customer c

How can I avoid this. How do I prevent repetition and increase query performance.

CodePudding user response：

Create a VIEW to reuse the base SELECT, eg

create view vCustomer
as
select
c.first_name,
c.middle_name,
c.last_name,
c.birth_day,
c.birth_month,
c.birth_year,
c.marital_status,
c.education_status,
c.departmant,
c.create_date,
c.create_user,
c.modify_date,
c.modify_user
from customer c

Then you can run queries like:

select * 
from vCustomer c 
where c.place='london' 
  and c.create_date>getdate()-100

CodePudding user response：

You could use an inline Table Valued Function. This acts like a parameterized view (and performs just as well, if not better)

CREATE OR ALTER FUNCTION GetCustomer
(
  @place varchar(100),
  @fromCreateDate datetim
)
RETURNS TABLE AS RETURN

select
  c.first_name,
  c.middle_name,
  c.last_name,
  c.birth_day,
  c.birth_month,
  c.birth_year,
  c.marital_status,
  c.education_status,
  c.departmant,
  c.create_date,
  c.create_user,
  c.modify_date,
  c.modify_user
from customer c 
where c.place = @place
  and c.create_date > @fromCreateDate;

GO

You would use it like this

SELECT *
FROM GetCustomer( 'london', DATEADD(day, -100, getdate()) ) c