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

How do I test a Table-Valued Function in SQL Server Management Studio?(如何在 SQL Server Management Studio 中测试表值函数?)
本文介绍了如何在 SQL Server Management Studio 中测试表值函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

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

功能代码如下:

<前>使用 [DB_NAME]走设置 ANSI_NULLS ON走设置 QUOTED_IDENTIFIER ON走更改功能 [dbo].[fn_GetConfigurationByProfile](@profileName AS NVARCHAR(50))退货表作为返回(-- 用结果集的行填充表变量选择 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_valueFROM system_settings_profiles加入 system_settings_valuesON system_settings_profiles.ssp_id = system_settings_values.ssv_ssp_id加入 system_settings_namesON system_settings_names.ssn_id = system_settings_values.ssv_ssn_id加入 system_settings_groupsON system_settings_groups.ssg_id = system_settings_names.ssn_ssg_idWHERE system_settings_profiles.ssp_name = @profileName)

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

这可能是一个业余问题,但好吧.我需要帮助:)

解决方案

你想使用FROM

例如:

选择...FROM fn_GetConfigurationByProfile('DEFAULTPROFILE')

SQL Server 用户定义函数

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.

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')?

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

解决方案

you want to use FROM

E.g :

select ...

    FROM fn_GetConfigurationByProfile('DEFAULTPROFILE')

SQL Server User-defined Functions

这篇关于如何在 SQL Server Management Studio 中测试表值函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)