创建和SQL Server
执行功能我有四个表:创建和SQL Server
dbo.Projects
-
dbo.Locations
(ID,地点名称) -
dbo.Purpose
(ID,Purposename) -
dbo.Types
(ID,typname)
我有一个搜索条件,这个条件是填充数据库表中的数据:locations
,purpose
和types
。
我想创建一个函数,该函数根据其他表从projects
的搜索结果返回表。我创建了一个,但它并没有做我所需要的:
ALTER FUNCTION SearchProjects
(
@location nvarchar(50),
@purpose nvarchar(50),
@type nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.Projects.ProjectName, dbo.Projects.Areas,
dbo.Projects.PaymentSystem, dbo.Projects.ReceivedDate,
dbo.Projects.PropertyClassification,
dbo.Projects.ProjectImage
FROM dbo.Locations INNER JOIN
INNER JOIN dbo.Projects ON dbo.Locations.ID = dbo.Projects.ID
INNER JOIN dbo.Purpose ON dbo.Locations.ID = dbo.Purpose.ID
INNER JOIN dbo.Types ON dbo.Locations.ID = dbo.Types.ID
WHERE (Projects.ProjectName like N'%'+ @location +'%'
and Purpose.PurposeName = N'%'+ @purpose +'%'
and Types.TypeName like N'%'+ @type +'%')
)
GO
SELECT * FROM dbo.SearchProjects('',' ','');
我是SQL SERVER的新手,所以不胜感激。
我会去:
ALTER FUNCTION SearchProjects (
@location NVARCHAR(50),
@purpose NVARCHAR(50),
@type NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT p.ProjectName,
p.Areas,
p.PaymentSystem,
p.ReceivedDate,
p.PropertyClassification,
p.ProjectImage,
l.LocationName,
pur.PurposeName,
t.TypeName
FROM dbo.Projects AS p
LEFT JOIN dbo.Locations AS l ON p.LocationID = l.ID
LEFT JOIN dbo.Purposes pur ON p.PurposeID = pur.ID
LEFT JOIN dbo.[Types] AS t ON p.TypeID = t.ID
WHERE UPPER(ISNULL(l.LocationName,N'')) LIKE N'%' + UPPER(@location) + '%'
AND UPPER(ISNULL(pur.PurposeName,N'')) LIKE N'%' + UPPER(@purpose) + '%'
AND UPPER(ISNULL(t.TypeName,N'')) LIKE N'%' + UPPER(@type) + '%'
)
GO
,如果你只想返回项目其中全部满足的条件并且输入参数中的空字符串被视为通配符:
SELECT * FROM dbo.SearchProjects('','',''); -- Returns all records
SELECT * FROM dbo.SearchProjects('north','',''); -- Returns all records with LocationName containing 'north'
SELECT * FROM dbo.SearchProjects('','research',''); -- Returns all records with PurposeName containing 'research'
SELECT * FROM dbo.SearchProjects('','','closed'); -- Returns all records with TypeName containing 'closed'
SELECT * FROM dbo.SearchProjects('north','research',''); -- Returns all records with LocationName containing 'north' and PurposeName containing 'research'
当将输入参数值与表中的字段值进行比较时,这还会删除任何区分大小写。我仍然会使用LEFT JOIN
而不是INNER JOIN
,以防万一某些项目记录可能存在错误的LocationID,PurposeID或TypeID值。
如果你想回到那里从输入参数标准的任何满足(当至少一个输入参数包含的值不把空的输入参数作为通配符)项目,你可以改变AND
S IN在WHERE
子句OR
S和传递NULL
任何输入参数,你不希望为其指定值:
ALTER FUNCTION SearchProjects (
@location NVARCHAR(50),
@purpose NVARCHAR(50),
@type NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT p.ProjectName,
p.Areas,
p.PaymentSystem,
p.ReceivedDate,
p.PropertyClassification,
p.ProjectImage,
l.LocationName,
pur.PurposeName,
t.TypeName
FROM dbo.Projects AS p
LEFT JOIN dbo.Locations AS l ON p.LocationID = l.ID
LEFT JOIN dbo.Purposes pur ON p.PurposeID = pur.ID
LEFT JOIN dbo.[Types] AS t ON p.TypeID = t.ID
WHERE UPPER(ISNULL(l.LocationName,N'')) LIKE N'%' + UPPER(@location) + '%'
OR UPPER(ISNULL(pur.PurposeName,N'')) LIKE N'%' + UPPER(@purpose) + '%'
OR UPPER(ISNULL(t.TypeName,N'')) LIKE N'%' + UPPER(@type) + '%'
)
GO
SELECT * FROM dbo.SearchProjects('','',''); -- Returns all records
SELECT * FROM dbo.SearchProjects('north',NULL,NULL); -- Returns all records with LocationName containing 'north'
SELECT * FROM dbo.SearchProjects(NULL,'research',NULL); -- Returns all records with PurposeName containing 'research'
SELECT * FROM dbo.SearchProjects(NULL,NULL,'closed'); -- Returns all records with TypeName containing 'closed'
SELECT * FROM dbo.SearchProjects('north','research',NULL); -- Returns all records with LocationName containing 'north' or PurposeName containing 'research'
NULL
荷兰国际集团不需要输入参数是必要的,因为在这里调用函数中的任何一个空字符串输入参数将导致全部重新正在退回的电源线:
SELECT * FROM dbo.SearchProjects('north','','');
SELECT * FROM dbo.SearchProjects('north','research','');
SELECT * FROM dbo.SearchProjects('north','','closed');
这些连接看起来不合适我。你没有完全指定你的模式,所以我猜测,但有一个使用这些连接的情况是非常不寻常的。
在这个示例中,我更改了为每个连接指定的列,但我猜测列中的列可能会被命名。
Alter FUNCTION SearchProjects (
@location nvarchar(50),
@purpose nvarchar(50),
@type nvarchar(50))
RETURNS TABLE
AS
RETURN
(
SELECT p.ProjectName,
p.Areas,
p.PaymentSystem,
p.ReceivedDate,
p.PropertyClassification,
p.ProjectImage
FROM dbo.Projects As p
LEFT JOIN dbo.Locations As l ON p.LocationID = l.ID
LEFT JOIN dbo.Purpose ON p.PurposeID = Purpose.ID
LEFT JOIN dbo.Types As t ON p.TypeID = t.ID
WHERE (l.LocationName like N'%'+ @location +'%'
OR Purpose.PurposeName = N'%'+ @purpose +'%'
OR t.TypeName like N'%'+ @type +'%')
)
GO
SELECT * FROM dbo.SearchProjects('',' ','');
很抱歉,如果我错过了问题或显示注释东西,这不是答案...
这不是假设'Projects'表的每个'LocationId','PurposeId'和'TypeID'字段中的值都与'Locations.ID','Purpose.ID'或'Types.ID'分别?我认为我们应该在这里使用'LEFT OUTER JOIN'而不是'INNER JOIN'。除非[hashim](http://*.com/users/6704418/hashim)可以确认'Projects'中的任何给定记录的每个连接表中都会有记录。 – 3N1GM4
并且调用'SELECT * FROM dbo.SearchProjects('','','');'将只返回关联的PurposeName包含空格的记录。 – 3N1GM4
关于'LEFT'和'INNER'的好处 – mendosi
我想仅搜索不保存或将记录插入到数据库中 – hashim
您是否有错误消息? –
没有表没有数据返回我不知道是否选择语句是否为真我想从项目表中选择项目取决于其他表 – hashim