How to prevent data value transfer is value equal to 0 in sql?

hasan25x 0
Viewed 42

I have a stock program. I just want to make the transfer between locations. For example, FROM A loc. to B Location 5 computers sent but at A location has no computers. I want to show a message on the page: 'There is no device to transfer and. For now, when I transfer it at SQL at A location:-5 computers, and at B location shows 5 computers. I can't prevent it.

I am trying something like this:

Create Proc Deneme4
(@StockID    NVARCHAR(100)=NULL,    
     @FROM       NVARCHAR(60)=NULL,
     @TO       NVARCHAR(60)=NULL,
     @CNT           integer= NULL)
AS
BEGIN
DECLARE @HasExist integer
Select @HasExist = COUNT(1) from STOK_TABLO Where URUNID=@StockID and LOKASYONID=@FROM
IF( @CNT != 0 and @FROM != '' )
BEGIN

UPDATE STOK_TABLO SET ADET=ADET-@CNT WHERE URUNID=@StockID AND LOKASYONID=@FROM
UPDATE STOK_TABLO SET ADET=ADET+@CNT WHERE URUNID=@StockID AND LOKASYONID=@TO
END
ELSE

print 'COUNT is 0 there is no possibility to transfer!'

END

STOCKID      MODELID    LOCATIONID  COUNT
DS6878HD    DS6878HD    CCR           0
DS6878HD    DS6878HD    CPM           45

That is my database also. I want to prevent it if the count is equal to 0. And also I don't want to see a count less than 0.

I am using SQL server 2012

asp.net sql-server c# sql
1 Answers

It seems you want to sum the existing value of ADET first. Logically you would also want to check if the new value would be lower than 0.

Also

  • Do not PRINT messages, these are mainly for debugging. Instead use THROW
  • You probably want a transaction around the whole thing.
    • Therefore, to avoid deadlocks, you want a UPDLOCK locking hint on the SELECT
    • You also want SET XACT_ABORT ON to ensure transactions are cleaned up.
  • Seems pointless to allow nulls in the parameters
CREATE OR ALTER PROC Deneme4
  @StockID    NVARCHAR(100),    
  @FROM       NVARCHAR(60),
  @TO         NVARCHAR(60),
  @CNT        integer
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

IF( ISNULL(
    (
    SELECT SUM(ADET)
    FROM STOK_TABLO WITH (UPDLOCK)
    WHERE URUNID = @StockID
      AND LOKASYONID = @FROM
    ), 0) - @CNT < 0
)
BEGIN
    THROW 50001, N'COUNT is too low, not possible to transfer!', 0;
END;

UPDATE STOK_TABLO
SET ADET += CASE WHEN LOKASYONID = @FROM THEN @CNT ELSE -@CNT END
WHERE URUNID = @StockID
  AND LOKASYONID IN (@FROM, @TO);

IF @@ROWCOUNT <> 2
    THROW 50002, N'Less than 2 rows affected!', 0;

COMMIT;
Charlieface0
Related