gridview 实现多字段综合查询-技术开发专区

gridview 实现多字段综合查询

作者：ITPUB论坛编辑： nancy 2008-05-23 00:00

【IT168技术文档】

最近写一个模糊综合多个字段的数据表查询，又要保持gridview的现有的功能。如添加，删除，更新，排序。为了简单少些代码，发现如果文本框不填的化，它一般会返回null 或空值，这样就可以使用 like ‘% @name %' 实现模糊查询，也可实现多个字段的查询。

数据库表：

CREATE TABLE [dbo].[tq_xianhuo](
[supplyuint] [nvarchar](250) NULL,
[telephone] [nvarchar](50) NULL,
[name] [nvarchar](255) NULL,
[quality] [nvarchar](255) NULL,
[specific] [nvarchar](255) NULL,
[sum] [nvarchar](255) NULL,
[place] [nvarchar](255) NULL,
[factory] [nvarchar](255) NULL,
[data] [datetime] NULL,
[beizhu] [nvarchar](250) NULL,
[id] [int] NOT NULL,
[dp_id] [int] NULL,
CONSTRAINT [PK_tq_xianhuo] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

存储过程：

ALTER PROCEDURE dbo.xian_list
@supplyuint nvarchar(100),
@name nvarchar(50),
@quality nvarchar(50),
@specific nvarchar(50),
@place nvarchar(50),
@factory nvarchar(50)
AS
if @supplyuint is null
set @supplyuint=''
if @name is null
set @name=''
if @quality is null
set @quality=''
if @specific is null
set @specific=''
if @place is null
set @place=''
if @factory is null
set @factory=''
select * from tq_xianhuo
where
supplyuint like '%'+@supplyuint+'%' and
[name] like '%'+@name+'%' and
quality like '%'+@quality+'%' and
specific like '%'+@specific+'%' and
place like '%'+@place+'%' and
factory like '%'+@factory+'%'
RETURN

关注我们