在 SQL Server 中创建和执行函数 [英] Create and execute function in SQL Server

问题描述

我有四张桌子:

1. dbo.Projects
2. dbo.Locations(id，位置名称)
3. dbo.Purpose(id，目的名称)
4. dbo.Types (id, typname)

1. dbo.Projects
2. dbo.Locations (id, location name)
3. dbo.Purpose (id, Purposename)
4. dbo.Types (id, typname)

我有一个搜索条件，这个条件填充了来自数据库表的数据:locations、 purpose 和 types.

I have a search criteria, this criteria is filled with data from database tables: locations, purpose and types.

我想创建一个函数，该函数从依赖于其他表的 projects 返回带有搜索结果的表.我创建了一个，但它不能满足我的需要:

I want to create a function that returns table with search result from projects dependent on other tables. I have created one but it does not do what I need:

 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 的新手，所以不胜感激.

I'm new to SQL SERVER so any help is appreciated.

推荐答案

我愿意:

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

如果您只想返回满足所有条件并且输入参数中的空字符串被视为通配符的项目:

if you want to return only Projects where all of the criteria are met and an empty string in an input parameter is treated as a wildcard:

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 值.

This also removes any case-sensitivity when comparing input parameter values to field values in your tables. I would still use LEFT JOIN instead of an INNER JOIN just in case some project records might have faulty LocationID, PurposeID or TypeID values.

如果您想返回满足输入参数中任何条件的项目(并且在至少一个输入参数包含值时不将空输入参数视为通配符)，您可以更改WHERE 子句中的 AND s 到 ORs 并为您不希望的任何输入参数传递 NULL指定一个值:

If you want to return Projects where any of the criteria from the input parameters are met (and not treat empty input parameters as wildcards when at least one input parameter contains a value), you could change the ANDs in the WHERE clause to ORs and pass NULL for any input parameters you don't wish to specify a value for:

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 在这里需要输入不需要的输入参数，因为在任何输入参数中使用空字符串调用函数将导致返回所有记录:

NULLing unwanted input parameters is necessary here because calling the function with an empty string in any of the input parameters would result in all records being returned:

SELECT * FROM dbo.SearchProjects('north','','');
SELECT * FROM dbo.SearchProjects('north','research','');
SELECT * FROM dbo.SearchProjects('north','','closed');

这篇关于在 SQL Server 中创建和执行函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！