1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dev
  • 921
  • 4
  • 14
  • 31
  • 1
    Does this answer your question? [Execute a large SQL script (with GO commands)](https://stackoverflow.com/questions/40814/execute-a-large-sql-script-with-go-commands) – SMor Aug 29 '20 at 17:53

1 Answers1

3

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??

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    thank you for your detailed response. You are correct that the print statement doesn't make a lot of sense. I was simply trying to reduce the problem down to something I could isolate. Regardless, removing `GO` did the trick. Thank you so much for your help. – Dev Aug 29 '20 at 18:14