用存储过程比较好
declare @sql varchar(1000)
set @sql='select * from tb1 where 1=1'
if 条件1<>''
set @sql=@sql+' and col1='+@colValue
if 条件2<>''
...
execute(@sql)
我写了一个查询语句,但是不行,各位帮我看看
CREATE PROCEDURE [dbo].[qt_getemployeesinfo]
@ID varchar(12),
@NAME varchar(50),
@GZ varchar(6),
@COM varchar(12),
@DEPT varchar(6)
AS
declare @strSql varchar(1000)
declare @MN3011 varchar(6)
declare @MN2001 varchar(6)
declare @MN2002 varchar(6)
declare @MN1003 varchar(6)
set @MN3011='MN3011'
set @MN2001='MN2001'
set @MN2002='MN2002'
set @MN1003='MN1003'
set @strSql='SELECT BS_Employees.YGID as 工号, BS_Employees.YGName as 姓名, BS_Employees.ENGName as 英文名,
c.DictDName AS 性别, BS_Employees.GSID as 所属公司, a.DictDName as 所属部门, b.DictDName AS 职务,
BS_Employees.LXDH as 联系电话, BS_Employees.DZAddress as 联系地址, BS_Employees.RZRQ as 入职日期,
d.DictDName AS 状态
FROM BS_Employees INNER JOIN
BS_SysDictD a ON BS_Employees.DeptID = a.DictDID INNER JOIN
BS_SysDictD b ON BS_Employees.YGGZ = b.DictDID INNER JOIN
BS_SysDictD c ON BS_Employees.XBSex = c.DictDID INNER JOIN
BS_SysDictD d ON BS_Employees.ZTState = d.DictDID
WHERE (a.DictMNID = '
set @strSql=@strSql+@MN3011+') AND (b.DictMNID = '
set @strSql=@strSql+@MN2001+') AND (c.DictMNID = '
set @strSql=@strSql+@MN2002+') AND (d.DictMNID = '
set @strSql=@strSql+@MN1003+')'
if @ID <> '' BEGIN
set @strSql=@strSql+' AND Employees.YGID='+@ID
END
if @NAME <> '' BEGIN
set @strSql=@strSql+' AND Employees.YGName='+@NAME
END
if @GZ <> '' BEGIN
set @strSql=@strSql+' AND Employees.YGID='+@GZ
END
if @COM <> '' BEGIN
set @strSql=@strSql+' AND Employees.GSID='+@COM
END
if @DEPT <> '' BEGIN
set @strSql=@strSql+' AND Employees.DictDName='+@DEPT
END
execute(@strSql)
GO