問(wèn)題描述
我從未使用過(guò)數(shù)據(jù)庫(kù)函數(shù)，但我當(dāng)前的項(xiàng)目需要它.我需要將一個(gè)常見(jiàn)的 sql 查詢放入一個(gè)函數(shù)中，這樣我們就不必在我們的代碼中輸入數(shù)百次.我已經(jīng)創(chuàng)建了函數(shù)，但我不知道如何使用它.
功能代碼如下:
<前>使用 [DB_NAME]走設(shè)置 ANSI_NULLS ON走設(shè)置 QUOTED_IDENTIFIER ON走更改功能 [dbo].[fn_GetConfigurationByProfile](@profileName AS NVARCHAR(50))退貨表作為返回(-- 用結(jié)果集的行填充表變量選擇 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') 嗎?
這可能是一個(gè)業(yè)余問(wèn)題，但好吧.我需要幫助:)
你想使用FROM
例如:
選擇...FROM fn_GetConfigurationByProfile('DEFAULTPROFILE')SQL Server 用戶定義函數(shù)
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
這篇關(guān)于如何在 SQL Server Management Studio 中測(cè)試表值函數(shù)?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！