[Amezarak]

> In MS SQL Server, a CREATE PROCEDURE statement cannot appear halfway through a batch of SQL statements. There's no good reason for this, it's just an arbitrary limitation. It means that extra manual steps are often required to execute a large batch of SQL. Manual steps increase risk and reduce efficiency.

It's been a while, but I am pretty sure all you have to do is put GO before/after the CREATE PROCEDURE. I'm absolutely positive there's some way around it, because I've run many, many such scripts on SQL Server without manual intervention.

EDIT: Yes, I just fired up a VM and ran this and got the expected results with no errors.

CREATE DATABASE HackerNews;

CREATE TABLE dbo.Test (id int IDENTITY(1, 1), name varchar(20));

GO

INSERT INTO dbo.Test (name) VALUES ('Amezarak');

GO

CREATE PROCEDURE dbo.sp_QueryTest AS SELECT * FROM dbo.Test;

GO

EXEC dbo.sp_QueryTest

In my personal opinion, MSSQL (including the tooling around it) is awesome and possibly one of Microsoft's best products. I actually regret not getting to use it anymore since a) my current job doesn't use it and b) I'm not shelling out for a license for my side projects. Postgres is definitely my next pick, though, and both are miles ahead of MySQL. I understand that MySQL is "good enough" for most people, but it's always painful going back to it and inevitably remembering almost all my favorite features don't exist. I'm stuck with it on a side project and it's frustrating.

[moron4hire]

The GO statement is an artifact of SQL Server Management Studio. It's not an actual T-SQL construct. So if you are executing scripts in an automated way (like a continuous deployment scenario), you have to have some way to manually split up the batches.

[Amezarak]

GO is also recognized by sqlcmd, so you're (potentially) still good with automation. I did actually run the above script in SSMS, but I ran most of the ones at my old job automated.

[mistermann]

You wouldn't perchance know a good online resource for recommendations on automating SQL Server deployments would you? Yes, you can google and piece things together, but I've yet to find a well written fairly comprehensive resource. (As the author notes, one would think perhaps MS would provide such documentation, but afaik they don't.)

[moron4hire]

It's all on MSDN, somewhere. TFA was incorrect that there isn't documentation available on SQL Server and T-SQL, but it's incredibly well documented, it's just MSDN's typical terrible discoverability.

[atwebb]

Yes, you can absolutely do that in MSSQL as long as there's a batch separator (it's not implicit at a line break,;, or something else. It's not uncommon to write large deployment scripts with proc creations/alters.

There's some other things like that in the post, some coming down to what seems like syntactic sugar and feels more like a difference that doesn't really need to be noted (like the types/dropping piece).

[chris_wot]

CREATE PROCEDURE puts a schema lock on the procedure. Didn't realise it doesn't release it! SQL Server only allows one Sch-M lock on an object at a time.

[salibhai]

GO creates a lot of headaches actually. If you are trying to do something in a transaction it breaks it

[swasheck]

why would you submit and terminate a batch halfway through a transaction? that doesnt really make a whole lot of sense so i cant really take this criticism seriously.