谁能出一个比这个简单的,效果一样的代码,不管你晕没晕,反正我没晕。ALTER PROCEDURE [dbo].[Product_browse]
@ID1 INT,
@ID2 INT,
@ID3 INT
ASdeclare @sql nvarchar(550)
if @ID1 is null
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" 1>1"
else
set @sql=" ID3 =" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID2=" + @ID2
else
set @sql=" ID2=" + @ID2 + " and ID3 =" + @ID3
end
end
else
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" ID1 =" + @ID1
else
set @sql=" ID1=" + @ID1 + " and ID3=" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID1=" + @ID1 + " and ID2 =" + @ID2 else
set @sql=" ID1=" + @ID1 + " and ID2 = " + @ID2+ " and ID3 ="+ @ID3
end
endset @sql="SELECT * FROM dbo.PRODUCT where " + @sqlexec sp_executesql @sql
@ID1 INT,
@ID2 INT,
@ID3 INT
ASdeclare @sql nvarchar(550)
if @ID1 is null
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" 1>1"
else
set @sql=" ID3 =" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID2=" + @ID2
else
set @sql=" ID2=" + @ID2 + " and ID3 =" + @ID3
end
end
else
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" ID1 =" + @ID1
else
set @sql=" ID1=" + @ID1 + " and ID3=" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID1=" + @ID1 + " and ID2 =" + @ID2 else
set @sql=" ID1=" + @ID1 + " and ID2 = " + @ID2+ " and ID3 ="+ @ID3
end
endset @sql="SELECT * FROM dbo.PRODUCT where " + @sqlexec sp_executesql @sql
SELECT * FROM dbo.PRODUCT
where ID1=ISNULL(@id,ID1) and ID2=ISNULL(@id2,ID2) and ID3=ISNULL(@id3,ID3)
ALTER PROCEDURE [dbo].[Product_browse]
@ID1 INT,
@ID2 INT,
@ID3 INT
ASdeclare @sql nvarchar(550)
if @ID1 is null
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" 1>1"
else
set @sql=" ID3 =" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID2=" + @ID2
else
set @sql=" ID2=" + @ID2 + " and ID3 =" + @ID3
end
end
else
begin
if @ID2 is null
begin
if @ID3 is null
set @sql=" ID1 =" + @ID1
else
set @sql=" ID1=" + @ID1 + " and ID3=" + @ID3
end
else
begin
if @ID3 is null
set @sql=" ID1=" + @ID1 + " and ID2 =" + @ID2
else
set @sql=" ID1=" + @ID1 + " and ID2 = " + @ID2+ " and ID3 ="+ @ID3
end
endset @sql="SELECT * FROM dbo.PRODUCT where " + @sqlexec sp_executesql @sql
无语的很无语!
create proc [dbo].[product_browse]
@id1 int=null,
@id2 int=null,
@id3 int=null
as
begin
select * from dbo.product
where id1=isnull(@id1,id1) and id2=isnull(@id2,id2) and id3=isnull(@id3,id3)
end
declare @sql nvarchar(550)
if (@ID1 is null) And (@ID2 is null) And (@ID3 is null)
return;
set @sql= 'SELECT * FROM dbo.PRODUCT where 1 =1'
if @ID1 is not null
set @sql = @sql + ' AND ' + 'ID1= ' + @ID1
if @ID2 is not null
set @sql = @sql + ' AND ' + 'ID2= ' + @ID2
if @ID3 is not null
set @sql = @sql + ' AND ' + 'ID3= ' + @ID3 exec sp_executesql @sql