Link to home
Start Free TrialLog in
Avatar of programmerist 1983
programmerist 1983Flag for Türkiye

asked on

How can i use WITH CTE for checking exist value?

Hi;

i wanna check cte has value or not. if cte has value i will add all cte to my table. But error occurs:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'IF'.

,
 WITH cte AS 
(
(
   SELECT
    0 ortalamastok,0 emniyetstok,'0061003742' CustomerID,c.MaterialNumber,getdate() InsertDate, GETDATE() UpdateDate , c.SapContractNumber 
   FROM
      ContractItems c 
   WHERE
      c.SapContractNumber = '3120012921' ) 
   EXCEPT
(
   SELECT
     0 ortalamastok,0 emniyetstok, '0061003742' CustomerID,e.MaterialNumber, getdate() InsertDate, GETDATE() UpdateDate,  e.SapContractNumber 
   FROM
      EmniyetStok e 
   WHERE
      e.SapContractNumber = '3120012921') 
)

IF  EXISTS (  SELECT * FROM cte ) 
begin
print 'add'
   INSERT INTO [dbo].[EmniyetStok]              
           ([ortalamastok]              
           ,[emniyetstok]              
           ,[CustomerID]              
           ,[MaterialNumber]    
		   ,[InsertDate]              
           ,[UpdateDate]              
           ,[SapContractNumber])
select
           c.[ortalamastok]              
           ,c.[emniyetstok]              
           ,c.[CustomerID]              
           ,c.[MaterialNumber]  
		   ,c.[InsertDate]              
           ,c.[UpdateDate]              
           ,c.[SapContractNumber]


from cte c

end
else
begin
print 'not add'
end
 

Open in new window

SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Since CTE not supports this type of IF EXISTS check, you can try an alternative with temp table
The below query queries database once and stores result in temp table and process further with that data

Modified query looks something like below

SELECT * INTO #ContractItems -- <-- REPLACE CTE WITH HASH TABLE
FROM
(
(
   SELECT
    0 ortalamastok,0 emniyetstok,'0061003742' CustomerID,c.MaterialNumber,getdate() InsertDate, GETDATE() UpdateDate , c.SapContractNumber 
   FROM
      ContractItems c 
   WHERE
      c.SapContractNumber = '3120012921' 
) 
   EXCEPT
(
   SELECT
     0 ortalamastok,0 emniyetstok, '0061003742' CustomerID,e.MaterialNumber, getdate() InsertDate, GETDATE() UpdateDate,  e.SapContractNumber 
   FROM
      EmniyetStok e 
   WHERE
      e.SapContractNumber = '3120012921'
	) 
)

IF  EXISTS (  SELECT * FROM #ContractItems )  -- <-- REPLACE CTE WITH HASH TABLE 
begin
print 'add'
   INSERT INTO [dbo].[EmniyetStok]              
           ([ortalamastok]              
           ,[emniyetstok]              
           ,[CustomerID]              
           ,[MaterialNumber]    
		   ,[InsertDate]              
           ,[UpdateDate]              
           ,[SapContractNumber])
select
           c.[ortalamastok]              
           ,c.[emniyetstok]              
           ,c.[CustomerID]              
           ,c.[MaterialNumber]  
		   ,c.[InsertDate]              
           ,c.[UpdateDate]              
           ,c.[SapContractNumber]


from #ContractItems c

end
else
begin
print 'not add'
end
drop table #ContractItems

Open in new window


Raj