无法使用 System.Data.SqlClient 在 SQL Server 中创建多个用户定义的函数

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试从同一个 .sql 文件中创建多个用户定义的函数.我正在使用 SQL Server 并使用 C# 的 System.Data 中的 SqlClient 执行我的查询.

I am trying to create multiple user defined functions from within the same .sql file. I am using SQL Server and am executing my queries using the SqlClient from C#'s System.Data.

.sql 文件的内容:

Contents of the .sql file:

CREATE FUNCTION [dbo].[GetUserId] (@username VARCHAR(32))
RETURNS INT
AS
BEGIN
    DECLARE @userId INT = -1

    SET @userId = (SELECT DISTINCT UserId FROM Users WHERE UserName = @username)

    RETURN @userId
END
GO

CREATE FUNCTION [dbo].[GetUserId2] (@username2 VARCHAR(32))
RETURNS INT
AS
BEGIN
    DECLARE @userId2 INT = -1

    SET @userId2 = (SELECT DISTINCT UserId FROM Users WHERE UserName = @username2)

    RETURN @userId2
END

这是我执行语句时抛出的错误:

Here's the error that is thrown when I execute the statement:

System.Data.SqlClient.SqlException: 'GO' 附近的语法不正确.
必须声明标量变量@username2".
'END' 附近的语法不正确.

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'GO'.
Must declare the scalar variable "@username2".
Incorrect syntax near 'END'.'

有什么想法吗?总的来说，我是 SQL 的新手，但这对我来说似乎是缺乏对语法/批处理的理解.

Any ideas? I'm new to SQL in general but this seems to be a lack of understanding syntax/batching to me.

我注意到GO"是 SQL Server Management Studio 的一部分，而不是 SqlClient.如果我从 .sql 文件中删除GO"，则会收到此错误:

It has come to my attention that 'GO' is part of SQL Server Management Studio, and not the SqlClient. If I remove the 'GO' from my .sql file, then I get this error:

'CREATE FUNCTION' 必须是查询批处理中的第一条语句.

'CREATE FUNCTION' must be the first statement in a query batch.

如何在不使用GO"的情况下分隔 CREATE FUNCTION 语句?

How do I separate CREATE FUNCTION statements without using 'GO'?

推荐答案

您不能在单个语句中运行多个批处理.

You cannot run multiple batches in a single statement.

我建议您使用 GO 拆分 TSQL 语句，然后逐个执行批处理.

I would suggest you to split your TSQL statement using GO and then execute the batches one by one.

string multipleUDFs = "CREATE FUNCTION... " +
"GO" + 
"CREATE FUNCTION ";
List<string> statementsToExecute = multileUDFs.Split("GO").ToList();

// Create the command 
var command = new SqlCommand(myConnection);

foreach(string sqlcommand in statementsToExecute)
{

// Change the SQL Command and execute
command.CommandText = sqlcommand;
command.ExecuteNonQuery();
}

相关文章