在 SQL 2005 的存储过程中创建用户定义的函数 [英] Creating a User Defined function in Stored Procedure in SQL 2005

问题描述

我有一个存储过程，我想在其中创建一个用户定义的函数 - 拆分(拆分用分隔符分隔的字符串并返回表中的字符串)，使用该函数并最终删除该函数.

I have a stored procedure in which i want to create a user defined function - Split (splits a string separated with delimiters and returns the strings in a table), make use of the function and finally drop the function.

我的问题是我是否可以在存储过程中创建一个用户定义的函数并最终删除它?

My question is that whether i can create a user defined function inside a stored procedure and drop it finally?

谢谢.

问候自然电压

推荐答案

从技术上讲……是的，你可以，但这并不意味着你应该.您必须小心避免使用 GO 语句(只需对每个批次使用 Exec)，但您可以执行以下操作:

Technically...yes you could but that does not mean you should. You would have to be careful about avoiding GO statements (just use Exec for each batch) but you could do something like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.Test
AS

Declare @Sql nvarchar(max)

Set @Sql = 'CREATE FUNCTION dbo.Foo
(   
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT 0 As Bar
)'

Exec(@Sql)

Select * 
From dbo.Foo()


Set @Sql = 'Drop Function dbo.Foo'
Exec(@Sql)

Return
GO
Exec dbo.Test

也就是说，我强烈建议不要使用这种解决方案，特别是如果您想要的函数是像 Split 函数这样有用的东西.我建议只创建 UDF 并使用它，直到您再次使用它为止.

That said, I would strongly recommend against this sort of solution, especially if the function you want is something that would be useful like a Split function. I would recommend just creating the UDF and using it and leaving it until you might use it again.

这篇关于在 SQL 2005 的存储过程中创建用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！