hello

I have two table

1. Model(model_id(PK), model_code, model_desp)

2. Maker(maker_id(PK),maker_code,maker_desp,model_id(FK))

Now I have Gridview in asp.net c#

MODEL TABLE :

model_id	model_code	Model_desp

MAKER TABLE:

Maker_id	Maker_desp	Model_code

Now I want to make crud stored procedure for the MAKER TABLE

PLZ help I spent my 3 days for finding and making solution

What does the procedure do? 

CREATE PROCEDURE sp1

AS

SELECT <columns> from Maker

i want to insert and update select and delete work I have done

CREATE PROCEDURE [dbo].[model_pro]
(
@mdid as int=0,
@mkid as int = 0,--for maker table PK, FK in this table And I dont want to display in gridview
@mkcd as char(5)='',--for maker table which I want to display in gridview
@mdcd as char(5)='',
@desp as char(15)='',
@status as varchar(50)=''
)AS
BEGIN
SET NOCOUNT ON;
if(@status = 'Display')
begin
--Select * from model--THESE FIELDS I WANT TO DISPLAY IN GRIDVIEW
select md.mdid,md.mdcd,md.desp,mk.mkcd
from model md left join maker mk
on md.mkid=mk.mkid
end
else if(@status = 'Add')
begin
--Insert into maker(mkcd) values (@mkcd)
--Insert into model(maker.mkcd,mdcd,desp) values (@mkcd,@mdcd,@desp)
if not exists(select md.mdid,md.mdcd,md.desp,mk.mkcd
from model md left join maker mk
on md.mkid=mk.mkid
where md.mdid=@mdid and
 --md.mdcd=@mdcd or  
 mk.mkcd=@mkcd -- or  --md.desp=@desp
 )
begin 
insert into model(mdcd,desp,mkid)
values(@mdcd,@desp, SCOPE_IDENTITY());
--insert into maker(mkcd)
--values(@mkcd);
end
else
RAISERROR('make model already exists',16,1);
end
else if(@status = 'Update')
begin
Update maker set mkcd=@mkcd where mkid=@mkid
Update model set mdcd=@mdcd, desp=@desp 
where mdid=@mdid
end
else if(@status = 'Delete')
begin
Delete from model where mdid=@mdid
end
END

• Proposed as answer by Michelle LiMicrosoft contingent staff Tuesday, January 27, 2015 1:04 PM
• Marked as answer by pituachModerator 3 hours 45 minutes ago

This is not the solution that I would choose :-), but it is a correct solution, and since the OP forgot to come back and response/close the thread, I will mark it as the answer in order to close the thread :-)

* Personally I would choose and recommend to work with separate SP for select / insert & update. Insert and Update can be done using MERGE statement :-)