sql语句 当查询条件为空时不作为条件 怎么写

[zhuzhu53320]

select * from usertable where name=@name and page=@page
我的要求是当name为空就不作为条件，即：
select * from usertable where page=@page
当page为空不作为条件，即：
select * from usertable where name=@name
当page，name都为空不作为条件，即
select * from usertable
怎么用一条sql语句写出来？？？

[姐只会简单SQL语句]

动态拼接。

[zhuzhu53320]

怎么动态拼接

[spiritofdragon]

select * from usertable where 1=1 and (name=@name or @name='') and (page=@page and @page='')

[姐只会简单SQL语句]

DECLARE @name VARCHAR(100),@page INT =1 SET @name='张三' DECLARE @sql VARCHAR(1000) SET @sql='select * from myTable where 1=1' IF @name IS NOT NULL SET @sql=@sql+' and name='''+@name+''''--字符型注意引号个数 IF @page IS NOT NULL SET @sql=@sql+' and page='+LTRIM(@page)--int类型注意转换为字符型 --PRINT @sql EXEC(@sql)

[spiritofdragon]

select * from usertable where 1=1 and (name=@name or @name='') and (page=@page or @page='')

[zhuzhu53320]

引用 5 楼 spiritofdragon 的回复:

select * from usertable where 1=1 and (name=@name or @name='') and (page=@page or @page='')

@name=''这里是name还是用@name？

[usbusb2007]

是@name

[lisession_li]

SELECT * FROM TB WHERE 1=1 AND ( (@NAME IS NOT NULL AND TB.NAME = @NAME ) OR @NAME IS NULL ) AND ( (@PAGE IS NOT NULL AND TB.PAGE = @PAGE) OR @PAGEIS NULL )