--添加班级存储过程create procedure proc_AddClass
@ClassName varchar(50)=null,
@ClassDate datetime,
@ClassCount int=0
asif @ClassName is null
begin
raiserror('请提供班级名称',16,1)
return
endif @ClassDate is null
begin
raiserror('请提供班级日期',16,1)
return
endif @ClassCount is null
begin
raiserror('请提供班级人数',16,1)
return
endinsert into Class values (@ClassName,@ClassDate,@ClassCount)
goexecute proc_AddClass '060118TS202','2006-1-18','22'
goselect * from Class
--班级查询
create procedure proc_SelClass
@ClassName varchar(50)
as
if @ClassName is null
begin
raiserror('请提供班级名称',16,1)
return
end
select * from Class where ClassName like @ClassName
goexecute proc_SelClass '060623TS201'
goselect * from Class
@ClassName varchar(50)=null,
@ClassDate datetime,
@ClassCount int=0
asif @ClassName is null
begin
raiserror('请提供班级名称',16,1)
return
endif @ClassDate is null
begin
raiserror('请提供班级日期',16,1)
return
endif @ClassCount is null
begin
raiserror('请提供班级人数',16,1)
return
endinsert into Class values (@ClassName,@ClassDate,@ClassCount)
goexecute proc_AddClass '060118TS202','2006-1-18','22'
goselect * from Class
--班级查询
create procedure proc_SelClass
@ClassName varchar(50)
as
if @ClassName is null
begin
raiserror('请提供班级名称',16,1)
return
end
select * from Class where ClassName like @ClassName
goexecute proc_SelClass '060623TS201'
goselect * from Class
begin
...
end
Create procedure proc_SelClass
@ClassName varchar(50)=null
@para varchar(50)=null
@para1 varchar(50)=nullas
set nocount on
declare @sql varchar(50)
select @sql='select * from class where 1=1 '
+case when @classname is null then '' else ' and ClassName like @ClassName' end
+case when @para is null then '' else ' and col1=@para' end
+case when @para1 is null then '' else ' and col2=@para' end
exec(@sql)
set nocount off
go
如果是多查询条件可以如下:
where
(@ClassName is null or ClassName=@ClassName)and
(@ClassDate is null or ClassDate=@ClassDate)and
(@ClassCount is null or ClassCount=@ClassCount)
(
@classname is null or classname = @classname
) and
(
@ClassDate is null or classname = @ClassDate
)
and
(@ClassCount is null or ClassCount=@ClassCount)
(
@classname is null or classname = @classname
) and
(
@ClassDate is null or classname = @ClassDate
)
and
(@ClassCount is null or ClassCount=@ClassCount)