ASP.NET - How to make sql stored procedre using if else and make false if condition is true

Asked By bhanupratap singh on 07-Feb-13 06:44 AM
Hi Every one
This is my procedure. I want to first Delete data if exist. After that I want to insert data into table
How to make false condition false, if data exist and delete statement runs for one time. After that It should excecute only insert statement . How to do it
thanks

ALTER procedure [dbo].[SP_Delivery_InsertUpdate]
(
@DELIVERY_STATION varchar(20)
,@DDRNO varchar(7) 
,@DDRDATE date  
--,@oldDELIVERY_STATION varchar(40)
 --   ,@oldDDRNO varchar(7)
,@CODE_NAME varchar  (20)  
,@CNMT  varchar  (7)  
,@NOPKG  varchar  (6)  
,@WEIGHT  varchar  (6)  
,@FREIGHT  varchar(14)
,@DC  varchar  (2)  
,@GATEPASS  varchar  (7)  
,@REBATE  varchar(10)
,@OTHER  varchar(10)
,@WGR  varchar  (1)  
,@EDATE  date    
,@OP_CODE  varchar  (1)  

)
as


begin
declare @Dele int
set @Dele=0;
IF EXISTS (SELECT * FROM DELIVERY  WHERE DELIVERY_STATION=@DELIVERY_STATION and DDRNO=@DDRNO) 

IF(@Dele=0)
BEGIN
delete from DELIVERY 
WHERE DELIVERY_STATION=@DELIVERY_STATION AND DDRNO=@DDRNO
set @Dele=1
END
else

if(@Dele=1)
BEGIN
insert INTO DELIVERY (DELIVERY_STATION ,DDRNO,DDRDATE,CODE_NAME,CNMT,NOPKG,WEIGHT,FREIGHT,DC,GATEPASS,REBATE,OTHER,WGR,EDATE,OP_CODE)
           values(@DELIVERY_STATION , @DDRNO,@DDRDATE,@CODE_NAME,@CNMT,@NOPKG,@WEIGHT,@FREIGHT,@DC,@GATEPASS,@REBATE,@OTHER,@WGR,@EDATE,@OP_CODE)
END
END
Robbe Morris replied to bhanupratap singh on 07-Feb-13 08:27 AM
Is there a reason you are deleting the row if it exists versus executing an update statement on the row?


bhanupratap singh replied to Robbe Morris on 07-Feb-13 11:43 PM
Thanks for reply
Yes there is reason . I m passing data of gridview to be inserted or updated. Suppose:- At first time user save data into table which are 10 rows. Later he wants to modify this entries with 15 rows or may save data into less than 10 rows.
Then what will happen. If user has less than 10 rows it means procedure will update only those rows which are being supplied. rest of rows which are in table will be as it was. Now when user search this record will get more than that which was saved last. Therefore I want to first delete if exist then run a insert stataement. If user insert 10 rows, there should be 10 rows. If user insert 5 rows then next time when user need to fetch the data, there should only 5 rows records.
It is bit complicated. 


Robbe Morris replied to bhanupratap singh on 08-Feb-13 08:35 AM
The code here is always either inserting a record or deleting itself first and always inserting itself again.  So, this business of only keeping x number of current records by executing this from another stored procedure isn't going to work.