CODEWITHSUNDEEP
sqlsql-serverview
user3074986
user3074986

Reputation: 33

sql view with declare

i need this in a view, but its seems that you cant use declare. help?

declare @lastsat datetime
set @lastsat = 
(select max(fechahoy) from [BigArea].[Thing].[Expanded]    where DiaSemana='Saturday')

SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
when a.fechahoy = @lastsat then 1
else 0
end as FinDeMEs
  FROM [BigArea].[Thing].[Expanded] a 
  join [BigArea].[dbo].[fechas_gestion] b 
  on a.fechahoy = b.fecha

Upvotes: 2

Views: 27615

Answers (4)

Suhel
Suhel

Reputation: 1

If you need parameters, performance & declare statements in your view then the alternative below might be worth considering.

Another alternative is to wrap the logic from your view in to a stored procedure that creates a table. The PROC could truncate & update or delete & recreate the table. If your table is large you can also create indexes on the table.

If you need to call the view/table in lots of places, you could wrap it around some logic that updates table if some conditions are met. e.g. only update the table once a day or once every 30min etc.

Understandably this could create code overhead because prior to every use of the view you'd need to check if it needs to be updated. But upshot is that yopu

Hope that helps.

Upvotes: 0

Farrokh
Farrokh

Reputation: 1167

you can create a Table Valued Function, then put your query inside it and in your view select it,

CREATE FUNCTION FUNCTION_NAME ( )
RETURNS  @retContactInformation TABLE
(
-- YOUR COUMN DEFINATIONS HERE
)
AS

    declare @lastsat datetime
set @lastsat = 
(select max(fechahoy) from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida]    where DiaSemana='Saturday')

SELECT a.*,
case
when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
when a.fechahoy = @lastsat then 1
else 0
end as FinDeMEs
  FROM [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] a 
  join [AreaComercial].[dbo].[fechas_gestion] b 
  on a.fechahoy = b.fecha

GO

then in your view :

SELECT * FROM FUNCTION_NAME()

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269783

For performance reasons, I would be inclined to join in the value:

select e.*,
       (case when g.fecha_gestion = e.fechahoy and month(fechahoy) <> month(getdate()) then 1
             when e.fechahoy = m.fechahoy then 1
             else 0
        end) as FinDeMEs
from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] e join
     [AreaComercial].[dbo].[fechas_gestion] g
      on e.fechahoy = g.fecha cross join
      (select max(fechahoy) as fechahoy
       from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida]
       where DiaSemana = 'Saturday'
      ) m;

I also suggest that you use table abbreviations for table aliases.

By the way, you can probably replace the logic with a window function:

select e.*,
       (case when g.fecha_gestion = e.fechahoy and month(fechahoy) <> month(getdate()) then 1
             when e.fechahoy = max(case when e.diasemana = 'Saturday' then e.fechahoy end)
             then 1
             else 0
        end) as FinDeMEs
from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] e join
     [AreaComercial].[dbo].[fechas_gestion] g
      on e.fechahoy = g.fecha;

This isn't 100% guaranteed, because the join could be doing some filtering. But it is likely to solve your problem efficiently.

Upvotes: 2

TMNT2014
TMNT2014

Reputation: 2130

Can't you just change your SQL to avoid the variable declaration?

SELECT a.*,
case
  when b.fecha_gestion = a.fechahoy and month(fechahoy)!=month(getdate()) then 1
  when a.fechahoy = (select max(fechahoy) from [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida]    where DiaSemana='Saturday')
    then 1
  else 0
end as FinDeMEs
FROM [AreaComercial].[LARRA_DOM\Mpollak].[Canales_expandida] a 
join [AreaComercial].[dbo].[fechas_gestion] b on a.fechahoy = b.fecha

Upvotes: 0

Related Questions