create procedure p
@searchText varchar(20)=null,
@Email varchar(20)=null,
@mobile varchar(20)=null,
@_phone varchar(20)=null,
@Address varchar(20)=null,
@_zip varchar(20)=null
asSELECT ID
, [UserName]
, RealName
, Phone
, Mobile
, Zip
, Address
, Email
, Mark
FROM Account
WHERE UserName like '%'+ISNULL(@searchText,'')+'%'
and
Email like '%'+ISNULL(@Email,'')+'%'
and
Mobile like '%'+ISNULL(@mobile,'')+'%'
and
Phone like '%'+ISNULL(@_phone,'')+'%'
and
Address like '%'+ISNULL(@Address,'')+'%'
and
Zip like '%'+ISNULL(@_zip,'')+'%'
@searchText varchar(20)=null,
@Email varchar(20)=null,
@mobile varchar(20)=null,
@_phone varchar(20)=null,
@Address varchar(20)=null,
@_zip varchar(20)=null
asSELECT ID
, [UserName]
, RealName
, Phone
, Mobile
, Zip
, Address
, Mark
FROM Account
WHERE UserName like '%'+ISNULL(@searchText,'')+'%'
and
Email like '%'+ISNULL(@Email,'')+'%'
and
Mobile like '%'+ISNULL(@mobile,'')+'%'
and
Phone like '%'+ISNULL(@_phone,'')+'%'
and
Address like '%'+ISNULL(@Address,'')+'%'
and
Zip like '%'+ISNULL(@_zip,'')+'%'
解决方案 »
- 求一个函数,参数是数字,结果返回 汉字的数字
- ! 求一个排序的问题
- 有两个表a 和b ,a.id=b.id,根据两个表的id,把a表的price替换为b表的price,这样的sql语句应该怎么写?
- 被载入代码了http://cn.daxia123.cn/cn.js
- 很棘手的~~四个值大小比较~有空进来瞧瞧
- 如何进行这种查询。
- 怎么样从一个数据库表中读取记录追加到另外一个数据库表中
- 紧急求助,input输入框中只能输入0-12数字,该怎么弄阿
- 什么实现WIN和LINUX下的MYSQL数据库互导????
- 几十万条查循纪录纪录,在WEB页面中分页显示问题?大虾帮忙!!!!!
- 问一个关于临时表的问题
- 请问三级封锁协议和两段锁协议 作用相同????????
SET @_Search = ' UserName like %' + @searchText + '%'
if ( @Email <> '' )
SET @_Search = ' and Email like %' + @Email + '%'
if ( @mobile <> '' )
SET @_Search = ' and Mobile like %' + @mobile + '%'
if( @_phone <> '' )
SET @_Search = ' and Phone like %' + @phone + '%'
if ( @Address <> '' )
SET @_Search = ' and Address like %' + @Address + '%'
if( @_zip <> '' )
SET @_Search = ' and like %' + @zip + '%'
declare @s as varchar(2000)
set @s='SELECT ID, [UserName], RealName,Phone,Mobile,Zip,Address,Email,Mark FROM Account'
set @s=@s+' where '+rtirm(@_Search)
exec(@s)
SET @_Search = ' and UserName like %' + @searchText + '%'
if ( @Email <> '' )
SET @_Search = ' and Email like %' + @Email + '%'
if ( @mobile <> '' )
SET @_Search = ' and Mobile like %' + @mobile + '%'
if( @_phone <> '' )
SET @_Search = ' and Phone like %' + @phone + '%'
if ( @Address <> '' )
SET @_Search = ' and Address like %' + @Address + '%'
if( @_zip <> '' )
SET @_Search = ' and like %' + @zip + '%'
-- SET @_name = '%' + @searchText + '%'
-- SET @_email = '%' + @Email + '%'
-- SET @_mobile = '%' + @mobile + '%'
--SET @_phone = '%' + @phone + '%'
--SET @_Address = '%' + @Address + '%'
--SET @_zip = '%' + @zip + '%' exec('INSERT INTO #SearchResultsTempTable (CustomerID, [UserName], RealName,Phone ,Mobile,Zip,Address,Email,Mark) SELECT ID, [UserName], RealName,Phone,Mobile,Zip,Address,Email,Mark
FROM Account
where 1=1 '+@_search)
@参数 as int
exec ('select * from 表名 where ID='+@参数)
如果是VARCHAR类型的
@参数 as int
exec ('select * from 表名 where ID=''+@参数+''')
直接通过程序给SQL参数就可以,不明白为什么用这么多的IF来判断
SET @_Search = ' and UserName like %' + @searchText + '%'
if ( @Email <> '' )
SET @_Search = ' and Email like %' + @Email + '%'
if ( @mobile <> '' )
SET @_Search = ' and Mobile like %' + @mobile + '%'
if( @_phone <> '' )
SET @_Search = ' and Phone like %' + @phone + '%'
if ( @Address <> '' )
SET @_Search = ' and Address like %' + @Address + '%'
if( @_zip <> '' )
SET @_Search = ' and like %' + @zip + '%'
declare @s as varchar(2000)
set @s='SELECT ID, [UserName], RealName,Phone,Mobile,Zip,Address,Email,Mark FROM Account where 1=1'
set @s=@s+rtirm(@_Search)
exec(@s)
insert into ...
select ....
from ....
where
UserName like (case @searchText when '' then username else '%'+ @searchText +'%' end)
and
Email like (case @Email when '' then Email else '%'+ @Email +'%' end)
and
其他的条件.
以上是所有条件.