Dapper - Stored Procedure - Incorrect syntax near 'GO'

Question

I have a stored procedure in a myscript.sql file that looks like this:

CREATE PROCEDURE [dbo].[_GetUserID]
    @EmailAddress NVARCHAR(254)
AS   
    DECLARE @UserID UNIQUEIDENTIFIER;

    SELECT @UserID = [ID] 
    FROM [dbo].[User] 
    WHERE [EmailAddress] = @EmailAddress

    PRINT @UserID    
GO

I have some C# code that relies on Dapper to run this script. I can successfully run this script when I copy-and-paste it into Azure Data Studio. However, when I am trying to run this script from code, I get an error:

Incorrect syntax near 'GO'

My C# code looks like this:

try
{
     var script = File.ReadAllText("<path to myScript.sql is here>");

     using (var connection = new SqlConnection(dbConnectionString))
     {
         var command = connection.CreateCommand();
         command.CommandText = script;
         command.CommandType = CommandType.Text;

         connection.Open();
         command.ExecuteNonQuery();
     }
                
     Console.WriteLine("Success.");
}
catch (Exception ex)
{
     Console.WriteLine($"Failed. Reason: '{ex.Message}')");
}

I don't understand why I can run myScript.sql from Azure Data Studio, however, it's not working from my C# code. I'm also creating tables using the same approach and it works fine. I'm not sure what I'm missing.

Answer 1

GO is not a valid T-SQL keyword - it's a separator that is used by SQL Server Management Studio and obviously also Azure Data Studio.

To fix this, just simply remove that GO line from your .sql script file and run it without this - should be just fine.

On a different note: having nothing but a PRINT statement in your stored procedure doesn't make a lot of sense - don't you want to actually SELECT @UserId to get that data sent back to the caller??