programmerist 1983
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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*/
...
Hi Programmerist,
Please try the complete code provided below-
Hope it helps!
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
--
Hope it helps!
IF EXISTS clause instead.