MVC存储过程

create table lei
(
lid int identity ,
lname varchar(30)
)
create table biao
(
bid int primary key identity ,
bname varchar(30) ,
age varchar(30) ,
lid int
)

select * from biao
select * from lei
--添加

create proc up_add
(
@bname varchar(30) ,
@age varchar(30) ,
@lid int
)
as
begin
insert into biao values(@bname,@age,@lid)
end

---修改--
if exists(select * from sysobjects where name='up_upt')
drop proc up_upt
go
create proc up_upt
@bid int,
@bname nvarchar(100),
@age int,
@lid nvarchar(100)
as
update biao set
bname=@bname,age=@age,lid=@lid where bid=@bid
go

----删除
create proc up_del
(
@bid int
)
as
delete from biao where bid=@bid

--查询---
alter proc up_cha
@bid varchar(10) ,
@bname varchar(30) ,
@age varchar(30) ,
@lid varchar(10)
as
begin
declare @sqls nvarchar(max)
set @sqls = 'select * from biao where 1=1';
if(@bid!='')
set @sqls +=' and bid = '''+@bid+'''';
if(@bname!='')
set @sqls += ' and bname = '''+@bname+'''';
if(@age!='')
set @sqls += ' and age ='''+@age+'''';
if(@lid!='')
set @sqls += ' and lid ='''+@lid+'''';

exec(@sqls)
end
go
exec up_cha '','','',''