Adding multiple default values programatically to SQL Server

Question

I have a table in multiple databases that I need to update and add default values for multiple columns within that table.

I've got the following constraints to add into the table:

ALTER TABLE ProcessQUeue
ADD CONSTRAINT C_bSaveAfter DEFAULT 0 FOR bSaveAfter;
GO 

ALTER TABLE ProcessQueue
ADD CONSTRAINT C_bForcePassword DEFAULT 0 FOR bForcePassword;
GO 

ALTER TABLE ProcessQueue
ADD CONSTRAINT C_bIsComplete DEFAULT 0 FOR bIsComplete;
GO

If I run the above in SQL Server Management Studio; it works great.

I then removed the constraints and tried running it through my code:

private static string AlterTable(string szQuery, string szReferencedFileName)
{
    try
    {
        using (SqlConnection aConnection = new SqlConnection(szConnectionString))
        {
            aConnection.Open();

            using (SqlCommand dbCommand = new SqlCommand(szQuery, aConnection))
            {
                dbCommand.ExecuteNonQuery();
            }
        }
    }
    //Some usual exception handling
    catch (SqlException e)
    {
        string szMsg = e.Message.ToString() + " " + e.ErrorCode.ToString();

        if (Environment.UserInteractive)
            Console.WriteLine("\r\n" + szMsg + "\r\n - Query: " + szQuery + "\r\n - " + szConnectionString + "\r\n");

        return "ERR:SQLDB/AddTable: " + szMsg;
    }
    catch (Exception e)
    {
        string szMsg = e.Message.ToString();

        if (Environment.UserInteractive)
            Console.WriteLine("\r\n" + szMsg + "\r\n - Query: " + szQuery + "\r\n - " + szConnectionString + "\r\n");

        return "ERRex:CheckSQLDB/AddTable: " + szMsg;
    }

    return string.Empty;
}

I get an error:

Incorrect syntax near 'go'.
Incorrect syntax near 'go'.
Incorrect syntax near 'for'.

In some cases, I've had to remove the GO from the query so I tried that as well and the syntax error is change from 'go' to 'for'.

Why would this work in SQL Server Management Studio and not in the C# application?

GO is a separator for SQL batches used by SQL Server Management Studio - but it's NOT a proper SQL statement - you cannot use this in code you execute from your C# app — marc_s
– marc_s, Commented Sep 17, 2016 at 6:38

Joel Coehoorn · Accepted Answer · 2016-09-17 04:51:52Z

4

GO is not valid SQL. That's why it doesn't work. It only a batch separator used by Management Studio. You can either put all those queries in a stored procedure or run them one by one.

edited Sep 17, 2016 at 4:51

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

answered Sep 17, 2016 at 4:23

Akshey Bhat

8,6872 gold badges24 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gratzy Over a year ago

I've also removed the go's and I get the error message listed above "incorrect syntax near for'. Do I have to do completely separate query calls one at a time for each value I want to add a default for or is there way to do this in one query? (It does work one at a time but that's a lot of queries - more than just the three above)

Gratzy · Accepted Answer · 2016-09-17 13:19:08Z

1

Thanks, Akshey. Armed with what you posted I figured out the correct syntax to add multiple defaults in one query. The format is this:

alter table ProcessQueue ADD
CONSTRAINT C_bSaveAfter DEFAULT 0 FOR bSaveAfter,
CONSTRAINT C_bForcePassword DEFAULT 0 FOR bForcePassword,
CONSTRAINT C_bIsComplete DEFAULT 0 FOR bIsComplete;

answered Sep 17, 2016 at 13:19

Gratzy

3,0833 gold badges37 silver badges60 bronze badges

Collectives™ on Stack Overflow

Adding multiple default values programatically to SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related