如何在 SQL Server Management Studio 中测试表值函数？

How do I test a Table-Valued Function in SQL Server Management Studio?

提问by Jeff

I've never worked with Database Functions, but my current project requires it. I need to put a common sql query into a function so we don't have to type it out in our code hundreds of times. I've got the function created, but I don't know how to use it.

我从未使用过数据库函数，但我当前的项目需要它。我需要将一个常见的 sql 查询放入一个函数中，这样我们就不必在我们的代码中输入数百次。我已经创建了函数，但我不知道如何使用它。

Here's the function code:

下面是函数代码：

USE [DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_GetConfigurationByProfile]
(
    @profileName AS NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN
    (
    -- Fill the table variable with the rows for your result set
    SELECT  system_settings_groups.ssg_id, system_settings_groups.ssg_name,
            system_settings_groups.ssg_parent_group_id, system_settings_names.ssn_name, 
            system_Settings_names.ssn_display_name, system_settings_values.ssv_value
    FROM    system_settings_profiles
    JOIN    system_settings_values
    ON      system_settings_profiles.ssp_id = system_settings_values.ssv_ssp_id
    JOIN    system_settings_names
    ON      system_settings_names.ssn_id = system_settings_values.ssv_ssn_id
    JOIN    system_settings_groups
    ON      system_settings_groups.ssg_id = system_settings_names.ssn_ssg_id
    WHERE   system_settings_profiles.ssp_name = @profileName
    )

So how would I use this in a sql query? Do I just use SELECT fn_GetConfigurationByProfile('DEFAULTPROFILE')?

那么我将如何在 sql 查询中使用它呢？我只使用 SELECT fn_GetConfigurationByProfile('DEFAULTPROFILE') 吗？

This may be an amateur question, but oh well. I need help :)

这可能是一个业余问题，但是很好。我需要帮助 ：）

回答by anishMarokey

you want to use FROM

你想使用FROM

E.g :

例如：

select ...

    FROM fn_GetConfigurationByProfile('DEFAULTPROFILE')

SQL Server User-defined Functions

SQL Server 用户定义函数

回答by devio

try this

尝试这个

SELECT * FROM dbo.fn_GetConfigurationByProfile('DEFAULTPROFILE')

回答by LukeH

SELECT *
FROM dbo.fn_GetConfigurationByProfile('DEFAULTPROFILE')

回答by MartW

You use it in the FROM clause, eg :

您可以在 FROM 子句中使用它，例如：

SELECT ....
FROM dbo.fn_GetConfigurationByProfile('DEFAULTPROFILE')

You can also join it to tables or use a where clause against it, among other things.

您还可以将它连接到表或使用 where 子句来反对它，等等。

回答by Robin Day

Others have shown how you can call your Table function within a standard query. However, can I suggest that you may prefer to create a View rather than a function?

其他人已经展示了如何在标准查询中调用 Table 函数。但是，我可以建议您可能更喜欢创建视图而不是函数吗？

CREATE VIEW [dbo].[ConfigurationView] AS
SELECT  
    system_settings_profiles.ssp_name,
    system_settings_groups.ssg_id,
    system_settings_groups.ssg_name,
    system_settings_groups.ssg_parent_group_id,
    system_settings_names.ssn_name, 
    system_Settings_names.ssn_display_name,
    system_settings_values.ssv_value
FROM    system_settings_profiles
JOIN    system_settings_values
ON      system_settings_profiles.ssp_id = system_settings_values.ssv_ssp_id
JOIN    system_settings_names
ON      system_settings_names.ssn_id = system_settings_values.ssv_ssn_id
JOIN    system_settings_groups
ON      system_settings_groups.ssg_id = system_settings_names.ssn_ssg_id

GO

Then you can use it in your SQL like this.

然后你可以像这样在你的 SQL 中使用它。

SELECT
    *
FROM
    ConfigurationView
WHERE
    ConfigurationView.ssp_name = 'DEFAULTPROFILE'

You will have the added options of indexing the view and also filtering on other data easily should you require it.

您将拥有索引视图的附加选项，如果需要，还可以轻松过滤其他数据。