---模糊查找---
if exists(select * from sysobjects where name='proc_fuzzySearch')
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID) where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
goproducts商品表 company公司表 product中CID是引用company表中的外键。想现实如我输入“钢铁”就能查出商品中有“钢铁”这个词和公司名称中有“钢铁”这个词的所有商品。用了两表联查。
错误信息
消息 156,级别 15,状态 1,过程 proc_fuzzySearch,第 4 行
关键字 'where' 附近有语法错误。
if exists(select * from sysobjects where name='proc_fuzzySearch')
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID) where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
goproducts商品表 company公司表 product中CID是引用company表中的外键。想现实如我输入“钢铁”就能查出商品中有“钢铁”这个词和公司名称中有“钢铁”这个词的所有商品。用了两表联查。
错误信息
消息 156,级别 15,状态 1,过程 proc_fuzzySearch,第 4 行
关键字 'where' 附近有语法错误。
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID) t
where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select Products.PID,Products.CID,Products.PName,company.CID,company.CName
from products,company
where Products.CID=company.CID and Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from
(
select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID
) t
where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
少了个表别名.(子查询在这里需要别名.)
@keyword nvarchar(20)
as
select Products.PID,Products.CID,Products.PName,company.CID,company.CName
from products,company
where Products.CID=company.CID
and (Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%')
go
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select Products.PID,Products.CID,Products.PName,company.CID,company.CName
from products,company
where Products.CID=company.CID and Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
if exists(select * from sysobjects where name='proc_fuzzySearch')
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select Products.PID,Products.CID,Products.PName,company.CID,company.CName
from products,company
where Products.CID=company.CID and (Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%')
go
if exists(select * from sysobjects where name='proc_fuzzySearch')
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID)t
where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
消息 8156,级别 16,状态 1,过程 proc_fuzzySearch,第 4 行
多次为 't' 指定了列 'CID'。
消息 4104,级别 16,状态 1,过程 proc_fuzzySearch,第 4 行
无法绑定由多个部分组成的标识符 "Products.PName"。
消息 4104,级别 16,状态 1,过程 proc_fuzzySearch,第 4 行
无法绑定由多个部分组成的标识符 "company.cname"。
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName,company.CID,company.CName from products,company where Products.CID=company.CID) a/*最好加个别名*/ where Products.PName like '%'+@keyword+'%' or company.cname like '%'+@keyword+'%'
go
drop proc proc_fuzzySearch
go
create proc proc_fuzzySearch
@keyword nvarchar(20)
as
select * from (select Products.PID,Products.CID,Products.PName as PName ,company.CID,company.CName as CName from products,company where Products.CID=company.CID) a/*最好加个别名*/ where a.PName like '%'+@keyword+'%' or a.cname like '%'+@keyword+'%'
go