Check table data Existing or not

Lalit Raghav

10y958

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 .

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

ketan borsdiya10y ago
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 (MDN,RES1,RES2,RES3,ChangeMode)
VALUES (S.MDN,S.RES1,S.RES2,S.RES3,S.ChangeMode)
WHEN MATCHED THEN
UPDATE SET
MDN=S.MDN,
RES1=S.RES1,
RES2=S.RES2,
RES3=S.RES3,
ChangeMode=S.ChangeMode;
END
Gagan Gautam10y ago
I give more suitable code after 8 P.m. so if you want so ping me
Lokesh Kumar10y ago
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