create proc dbo.test
@typeID varchar (50)
as
declare @sql varchar(1000)if @typeID is null
set @sql='select * from table1' --@typeID 等于null执行这个查询
else
set @sql='select * from table1 where 产品类别 ='+ @typeID --@typeID not null执行这个查询
exec(@sql)GO
@typeID varchar (50)
as
declare @sql varchar(1000)if @typeID is null
set @sql='select * from table1' --@typeID 等于null执行这个查询
else
set @sql='select * from table1 where 产品类别 ='+ @typeID --@typeID not null执行这个查询
exec(@sql)GO
@typeID varchar (50)
as
declare @sql varchar(1000)if @typeID is null
set @sql='select * from table1' --@typeID 等于null执行这个查询
else
set @sql='select * from table1 where 产品类别 ='''+ @typeID + '''' --@typeID not null执行这个查询
exec(@sql)GO
@typeID varchar (50)
as
declare @sql varchar(1000)if @typeID is null
set @sql='select * from table1' --@typeID 等于null执行这个查询
else
set @sql='select * from table1 where 产品类别 ='''+ @typeID + '''' --@typeID not null执行这个查询
exec(@sql)GO
@typeID varchar (50)=null
as
if @typeID is null
select * from table1
else
select * from table1 where 产品类别 = @typeID
go--或者:
create proc p_qry
@typeID varchar (50)=null
as
select * from table1 where @typeID is null or 产品类别 = @typeID
go
create proc p_qry
@typeID varchar (50)=null,
@name varchar(50)=null
as
set nocount on
declare @s Nvarchar(4000)
select @s
=case when @typeID is null then ''
else ' and 产品类别=@typeID' end
+case when @name is null then ''
else ' and 名称=@name' end,
@s='select * from table1'
+case @s when '' then ''
else ' where '+stuff(@s,1,4,'') end
exec sp_executesql @s
,N'@typeID varchar (50)=null,
@name varchar(50)=null'
,@typeID,@name
@typeID varchar (50)=null
as
if @typeID is null
select * from table1
else
select * from table1 where 产品类别 = @typeID
go
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId