小弟存储过程问题
有两个问题
1 第一个条件if @diskid is not null，赋值的话正常，不赋值的话变为0。我要的效果是忽略这个条件
2 时间参数如何搜索呢？
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pt_disksearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pt_disksearch]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE pt_disksearch
@diskid int=null,
@writedate datetime=null,
@detail varchar(5000)=null,
@disktype varchar(50)=null,
@gametype varchar(50)=null,
@singer varchar(500)=null,
@starring varchar(500)=null,
@movietype varchar(100)=null,
@moviecountry varchar(100)=null
AS
declare @sql as varchar(8000)
set @sql=''
if @diskid is not null
begin
set @sql=@sql+'and diskid='+cast(@diskid as varchar(20))+''
end
if @writedate is not null
begin
set @sql=@sql+'and writedate='''+cast(@writedate as varchar(20))+''''
end
if @detail is not null
begin
set @sql=@sql+'and detail like ''%'''+@detail+'''%'''
end
if @disktype is not null
begin
set @sql=@sql+' and disktype='''+cast(@disktype as varchar(20))+''''
end
if @gametype is not null
begin
set @sql=@sql+'and gametype='''+@gametype+''''
end
if @gs is not null
begin
set @sql=@sql+'and gs='''+@gs+''''
end
if @yy is not null
begin
set @sql=@sql+'and yy='''+ @yy+''''
end
if @movietype is not null
begin
set @sql=@sql+'and movietype='''+ @movietype+''''
end
if @moviecountry is not null
begin
set @sql=@sql+'and moviecountry='''+ @moviecountry+''''
end
if @sql<>''
begin
print @sql
exec(@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
问题点数：0、回复次数：4Top
1 楼James_ht（顺风飞扬）回复于 2004-09-04 09:45:29 得分 0
@diskid int =0Top
2 楼haoK（haoK.Y）回复于 2004-09-04 09:56:00 得分 0
1
这样使用@diskid
ISNULL(@diskid, 0)
或者在开头先处理一下：SET @diskid = ISNULL(@diskid, 0)
2
时间可以直接用> < =比较，between也可
CONVERT(CHAR(10), @datetime, 120)转换成'2004-09-04'形式，
别忘了前后加引号
Top
3 楼boboox（伟人西门庆）回复于 2004-09-12 16:55:21 得分 0
@diskid的值这样处理后还是0啊，还是要查找出diskid＝0的记录。
我要的效果是完全忽略这个条件。请赐教。。。Top
4 楼zjcxc（邹建）回复于 2004-09-12 17:23:25 得分 0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pt_disksearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pt_disksearch]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE pt_disksearch
@diskid int=null,
@writedate datetime=null,
@detail varchar(5000)=null,
@disktype varchar(50)=null,
@gametype varchar(50)=null,
@singer varchar(500)=null,
@starring varchar(500)=null,
@movietype varchar(100)=null,
@moviecountry varchar(100)=null
AS
declare @sql as Nvarchar(4000) --改为类型
select @sql
=case
when @diskid is null then ''
else ' and diskid=@diskid' end
+case
when @writedate is null then ''
else ' and writedate=@writedate' end
+case
when @detail is null then ''
else ' and detail like ''%''+@detail+''%''' end
+case
when @disktype is null then ''
else ' and disktype=@disktype' end
+case
when @gametype is null then ''
else ' and gametype=@gametype' end
/*--这里的变量没有定义,不知道是否楼主搞错了
+case
when @gs is null then ''
else ' and gs=@gs' end
+case
when @yy is null then ''
else ' and yy=@yy' end
--*/
+case
when @movietype is null then ''
else 'and movietype=@movietype' end
+case
when @moviecountry is null then ''
else ' and moviecountry=@moviecountry' end
,@sql='select * from 表' --至少有查询语句
+case @sql when '' then ''
else ' where '+stuff(@sql,1,5,'') end
exec sp_executesql @sql
,N'@diskid int=null, --存储过程中定义的参数帖一遍
@writedate datetime=null,
@detail varchar(5000)=null,
@disktype varchar(50)=null,
@gametype varchar(50)=null,
@singer varchar(500)=null,
@starring varchar(500)=null,
@movietype varchar(100)=null,
@moviecountry varchar(100)=null'
,@diskid,@writedate,@detail,@disktype,@gametype,@singer,@starring,@movietype,@moviecountry
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Top
