問題描述

我從未使用過數據庫函數，但我當前的項目需要它.我需要將一個常見的 sql 查詢放入一個函數中，這樣我們就不必在我們的代碼中輸入數百次.我已經創建了函數，但我不知道如何使用它.

功能代碼如下:

那么我將如何在 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 中測試表值函數?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！