SQL Incorrect Syntax Near 'GO'

Question

I am getting the error in my sproc and I cannot figure out why. I have looked at other, almost identical questions like this Here and the answers aren't doing the trick for me. the syntax error is at the 'Go' right after the database creation.

USE [DATABASENAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sproc]
@Id int
AS
BEGIN

SET NOCOUNT ON;

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 
N'Name')

create database Name;

GO

CREATE TABLE [Name].[dbo].[Account](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [int] NOT NULL
);

`GO` is not a qualified sql statement.. it is a batch separator for independent sql statements — RoMEoMusTDiE, May 03 '18 at 21:31
I understand that, but I need to separate the db creation and the table creation or I will get a "Database does not exist error". — Robert, May 03 '18 at 21:34
you can only create a database on the fly if you have sufficient rights and you are in the `master` database — RoMEoMusTDiE, May 03 '18 at 21:42
@Alfabravo Sorry, this is just a snippet of the beginning of the procedure. It is very long and the alter is closed. Also, "@Id" is not a variable, it is a parameter. — Robert, May 03 '18 at 21:59

Aaron Dietz · Accepted Answer · 2018-05-03T21:53:56.050

4

GO is not allowed in stored procedures. It separates batches and a procedure itself is one batch which cannot be separated.

You could use one procedure to create the database, then a second procedure to create the table.

Edit

Actually you could do it in one procedure:

CREATE PROCEDURE [dbo].[Sproc]
AS
BEGIN

EXEC ('USE [Master]; CREATE DATABASE [name]')
EXEC ('USE [Name]; CREATE TABLE [name].dbo.[Account] (id int)')

END

edited May 03 '18 at 21:53

answered May 03 '18 at 21:47

Aaron Dietz

10,137
1
14
26

I was thinking that may be the best way to do this. I appreciate your recommendation! – Robert May 03 '18 at 22:00

SQL Incorrect Syntax Near 'GO'

1 Answers1