CODEWITHSUNDEEP
sqlsql-servert-sql
SneakyTactician2
SneakyTactician2

Reputation: 117

Unable to Create Multiple User Defined Functions in SQL Server Using 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.

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: 'Incorrect syntax near 'GO'.
Must declare the scalar variable "@username2".
Incorrect syntax near 'END'.'

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

EDIT: 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' must be the first statement in a query batch.

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

Upvotes: 0

Views: 595

Answers (4)

istruble
istruble

Reputation: 13702

Another option is to make use (really ab-use) of sp_executesql to wrap your CREATE FUNCTION statements. It is not pretty but is another work around that I have used in situations where splitting the test and executing pieces is not an option.

exec sp_executesql N'
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'

execute sp_executesql N'
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'

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56745

In addition to Dan Guzman's answer using SMO, you can also split your SQL script text into separate SQL batches and execute each one in turn on the same connection. That is all that SSMS and SMO do anyway:

    /// <summary>
    /// Executes a script-like SQL string, using GOs to break it into
    /// seperate batches within the same session
    /// </summary>
    public void ExecSqlScript(string sqlScript, SqlConnection conn)
    {
        // change all line-breaks to LF
        string script = sqlScript.Replace("\r\n", "\n").Replace("\r", "\n");

        // split the script into separate batches using "GO"
        string[] batches = script.Split(new[] { "\nGO\n", "\ngO\n", "\nGo\n", "\ngo\n" }, StringSplitOptions.RemoveEmptyEntries);

        // execute each batch on the same connection/session
        foreach(string batch in batches)
        {
            SqlCommand cmd = new SqlCommand(batch, conn);
            cmd.ExecuteNonQuery();
        }
    }

Note: Untested

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46223

Below is an example using the Server Management Objects (SMO) API with the latest preview NuGet package. Unlike SqlClient, SMO can run scripts with GO batch terminators, similarly to SSMS and SQLCMD.

using System.IO;
using System.Data.SqlClient;
//reference latest NuGet preview package https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/160.1911221.0-preview#
using Microsoft.SqlServer.Management.Common;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("Data Source=.;Integrated Security=SSPI"))
            {
                var serverConnection = new ServerConnection(connection);
                connection.Open();
                var sqlScript = File.ReadAllText(@"C:\temp\ScriptWithGoBatchTerminators.sql");
                serverConnection.ExecuteNonQuery(sqlScript);
            }
        }
    }
}

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 12969

You cannot run multiple batches in a single statement.

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();
}

Upvotes: 2

Related Questions