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

asked on

how to stop adding the same data into my table by using stored precedure in msSql?

Hi;

i have  a strange problem. i am checking count(*) to prevent dublication of my inserted data. Because if i call below stored precedure , always "else begin ...... end " running and making dublicate records. i guess;

 select @count = count(*) from  EmniyetStok e where e.CustomerID = @customerId AND e.SapContractNumber= @sapContractNo        
if(@count>0)  

// ALWAYS RETURNING TO ME @COUNT=-1

But it is not true. if i run count : 704 how it is possible. i want to insert data only once when @count =0

User generated image



CREATE PROCEDURE [dbo].[GetDataByCustomerIdAndSapContractNum]          
 @sapContractNo varchar(1000),        
 @customerId varchar(1000)        
AS          
        
 BEGIN          
 declare @count int        
 select @count = count(*) from  EmniyetStok e where e.CustomerID = @customerId AND e.SapContractNumber= @sapContractNo        
if(@count>0)        
begin        
SELECT    
DISTINCT      
 NEWID() id,    
e.ortalamastok,         
e.EmniyetStok,         
e.CustomerID,         
e.MaterialNumber,         
e.EmniyetStokId,         
u.CalledQty,         
u.DataB,         
u.DatBI,         
u.OpenQty,         
u.SapContractNumber,         
u.MaterialName,         
u.PartType,         
u.TestAmn,        
c.CustomerFullName        
FROM EmniyetStok e         
INNER JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
WHERE u.SapContractNumber = @sapContractNo AND e.CustomerID = @customerId        
END        
else        
  begin        
declare @CustomerFullName nvarchar(500)        
   select  @CustomerFullName =  c.CustomerFullName  from Customers c where c.CustomerID = @customerId        
          
   INSERT INTO [dbo].[EmniyetStok]        
           ([ortalamastok]        
           ,[emniyetstok]        
           ,[CustomerID]        
           ,[MaterialNumber]        
           ,[InsertDate]        
           ,[UpdateDate]        
           ,[SapContractNumber])        
      SELECT        
        distinct    
                                isnull(e.ortalamastok,0) ortalamastok,         
                                isnull(e.EmniyetStok,0) EmniyetStok,         
                                @customerId CustomerID,         
                                isnull(u.MaterialNumber,0) MaterialNumber,         
                                GETDATE(),        
              GETDATE(),        
                                u.SapContractNumber        
                                       
                                FROM EmniyetStok e         
                                right JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
                                LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
                                WHERE u.SapContractNumber =  @sapContractNo        
        
        
        
  PRINT 'insert işlemi gerçekleşti.'      
        
 SELECT        
  NEWID() id,  
                                isnull(e.ortalamastok,0) ortalamastok,         
                                isnull(e.EmniyetStok,0) EmniyetStok,         
                                isnull(e.CustomerID,0) CustomerID,         
                                isnull(u.MaterialNumber,0) MaterialNumber,         
                                isnull(e.EmniyetStokId,0) EmniyetStokId,         
                                u.CalledQty,         
                                u.DataB,         
                                u.DatBI,         
                                u.OpenQty,         
                                u.SapContractNumber,         
                                u.MaterialName,         
                                u.PartType,         
                                u.TestAmn,        
                                @CustomerFullName CustomerFullName        
                                FROM EmniyetStok e         
                                right JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
                                LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
                                WHERE u.SapContractNumber =  @sapContractNo         
end        
        
 END 

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

you can try use

IF EXISTS clause instead.
ASKER CERTIFIED 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
a select count can never produce negative values, so there must be something else there.
the insert having a right join does not look right, btw, can you check if it should not be a normal join?
like:
CREATE PROCEDURE [dbo].[GetDataByCustomerIdAndSapContractNum]          
 @sapContractNo varchar(1000),        
 @customerId varchar(1000)        
AS          
        
 BEGIN 

/*Got record*/
IF EXISTS( select *  from  EmniyetStok e where e.CustomerID = @customerId AND e.SapContractNumber= @sapContractNo  )
begin

else /*Not exist*/
...

Open in new window

Hi Programmerist,
Please try the complete code provided below-

--

CREATE PROCEDURE [dbo].[GetDataByCustomerIdAndSapContractNum]  
(        
	 @sapContractNo varchar(1000),        
	 @customerId varchar(1000)        
) 
AS          
BEGIN          

	DECLARE @CustomerFullName nvarchar(500)        
	SELECT TOP 1 @CustomerFullName =  c.CustomerFullName from Customers c where c.CustomerID = @customerId        

	IF NOT EXISTS (  SELECT TOP 1 1 FROM EmniyetStok e WHERE e.CustomerID = @customerId AND e.SapContractNumber= @sapContractNo )    
	BEGIN

		INSERT INTO [dbo].[EmniyetStok]([ortalamastok],[emniyetstok],[CustomerID],[MaterialNumber],[InsertDate],[UpdateDate],[SapContractNumber])        
		SELECT      
		distinct isnull(e.ortalamastok,0) ortalamastok, isnull(e.EmniyetStok,0) EmniyetStok, @customerId CustomerID,         
		isnull(u.MaterialNumber,0) MaterialNumber, GETDATE(), GETDATE(), u.SapContractNumber        
		FROM EmniyetStok e         
		right JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
		LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
		WHERE u.SapContractNumber =  @sapContractNo 

		SELECT        
		NEWID() id,  
		isnull(e.ortalamastok,0) ortalamastok,         
		isnull(e.EmniyetStok,0) EmniyetStok,         
		isnull(e.CustomerID,0) CustomerID,         
		isnull(u.MaterialNumber,0) MaterialNumber,         
		isnull(e.EmniyetStokId,0) EmniyetStokId,         
		u.CalledQty,         
		u.DataB,         
		u.DatBI,         
		u.OpenQty,         
		u.SapContractNumber,         
		u.MaterialName,         
		u.PartType,         
		u.TestAmn,        
		@CustomerFullName CustomerFullName        
		FROM EmniyetStok e         
		right JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
		LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
		WHERE u.SapContractNumber =  @sapContractNo         
		
	END 
	ELSE
	BEGIN

		SELECT    
		DISTINCT      
		 NEWID() id,    
		e.ortalamastok,         
		e.EmniyetStok,         
		e.CustomerID,         
		e.MaterialNumber,         
		e.EmniyetStokId,         
		u.CalledQty,         
		u.DataB,         
		u.DatBI,         
		u.OpenQty,         
		u.SapContractNumber,         
		u.MaterialName,         
		u.PartType,         
		u.TestAmn,        
		c.CustomerFullName        
		FROM EmniyetStok e         
		INNER JOIN Urunler u ON e.MaterialNumber = u.MaterialNumber  AND u.SapContractNumber = e.SapContractNumber        
		LEFT JOIN Customers c ON c.CustomerID = e.CustomerID        
		WHERE u.SapContractNumber = @sapContractNo AND e.CustomerID = @customerId        

	END

END

--

Open in new window


Hope it helps!