nu stiu cat e de corect dar am facut asa:

am facut un view care-mi intoarce exact ce am eu nevoie....cu conditia IF ( DATEDIFF(dd,@DataFinalizareContract,@GETDATE()) = 4 )

am facut view pt ca problema sa complicat pe parcurs si nu mai era vorba doar de o tabela cum am crezut eu initial.

care arata asa:

create view vw_ListaMail
as

SELECT   pc.DataLivrarii,pc.DataEfectLivrare,su.InternalEmailAddress,vc.EUltimaVersiune,ncv.new_name

FROM     dbo.PRODUSCONTRACT pc
 INNER JOIN dbo.VERSIUNECONTRACT vc ON pc.VersiuneContractID = vc.VersiuneContractID
 INNER JOIN dbo.CONTRACT c ON vc.ContractID = c.ContractID
 INNER JOIN mscrm..New_ContractVanzari ncv on c.new_contractvanzariid = ncv.new_contractvanzariid
 INNER JOIN mscrm..systemuser su on ncv.ownerid = su.systemuserid

WHERE   pc.DataEfectLivrare is null
 and datediff(day,pc.DataLivrarii,getdate()) = 4
 and su.InternalEMailAddress is not null
 and vc.EUltimaVersiune = 1

dupa care am facut procedura de trimitere mail (pe care o voi pune intr-un job sa se execute zilnic) astfel:

create proc spr_TrimiteMailAvertizare_ContractTEST
as

DECLARE @DataLivrarii datetime, @InternalEmailAddress nvarchar (100), @new_name nvarchar(100)
declare @Mesaj varchar (250)

DECLARE contract_cursor CURSOR FOR
SELECT DataLivrarii,InternalEmailAddress,new_name FROM vw_ListaMail

OPEN contract_cursor

FETCH NEXT FROM contract_cursor
INTO @DataLivrarii, @InternalEmailAddress,@new_name

WHILE @@FETCH_STATUS = 0
BEGIN

 set @Mesaj = @new_name -- se modifica pe parcurs
  
 exec _SMTPemail 'alex@net.ro', @InternalEmailAddress,'Important',@Mesaj

   FETCH NEXT FROM contract_cursor
   INTO @DataLivrarii, @InternalEmailAddress,@new_name
END

CLOSE contract_cursor
DEALLOCATE contract_cursor
GO

cum vi se pare.....mai trebuie adaugat ceva....stiu ca se poate si mai bine? :)

multumesc mult

alex.