4 ms·
> 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 arbitrar
by Amezarak 12y ago
> 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 12y agoThe 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 12y agoGO 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 12y agoYou 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 12y agoIt'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 12y agoYes, 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).
- salibhai 12y agoGO creates a lot of headaches actually. If you are trying to do something in a transaction it breaks it
- swasheck 12y agowhy 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.
- deleted 12y ago[deleted]
- chris_wot 12y agoCREATE 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.