Check table data Existing or not

Question

Hi Friends i have a table now i want to create a store procedure and pass a table variable as parameter in store procedure . Table variable is same as database table . Now i want to that is mdn exist then delete that record and mdn not exit in table parameter then insert record in database .

ketan borsdiya · Answer

Hi Lalit I don't know why are you deleting row But you may also update it. for that First create user define table type to pass table in procedure and then create below procedure. It may help you CREATE PROCEDURE PRC_Test @table As dbo.UDTT_Test READONLY AS BEGIN MERGE AS T USING @table AS S ON T.MDN = S.MDN WHEN NOT MATCHED THEN INSERT (MDNRES1RES2RES3ChangeMode) VALUES (S.MDNS.RES1S.RES2S.RES3S.ChangeMode) WHEN MATCHED THEN UPDATE SET MDN=S.MDN RES1=S.RES1 RES2=S.RES2 RES3=S.RES3 ChangeMode=S.ChangeMode; END

Gagan Gautam · Answer

I give more suitable code after 8 P.m. so if you want so ping me

Lokesh Kumar · Answer

Please Try this Create PROCEDURE [dbo].[UpdateTable] @Id int @MDN varchar(50) @RES1 varchar(50) @RES2 varchar(1) @RES3 int @ChangeMode char(1) AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. BEGIN SET NOCOUNT ON; IF EXISTS(SELECT * FROM tablename WHERE ID=@ID) BEGIN DELETE FROM tablename WHERE ID=@ID END ELSE BEGIN INSERT INTO tablename (ID MDN RES1 RES1 RES1 ChangeMode ) VALUES(@ID @MDN @RES1 @RES2 @RES3 @ChangeMode ) END END