In SSMS if I want to create table and insert some values there with one query, I can separate those two commands with go; However I cannot use go in stored procedure. What can I do?
2 Answers
GO is a utility command that is not required in SPs.
When you use this command within common sql statements,SQL Server interprets it as a signal to send the current batch of Transact-SQL statements to an instance of SQL Server.
Write your 'Create Table ' and 'Insert' statements ordinally and put one 'GO' command at the end of your SP.
SQL server will create your table and then uses it in a single batch of Transact-SQL statements.
1 Comment
Dan Guzman
GO is not a T-SQL command and is not recognized by SQL Server. It is the client application (e.g. SSMS, SQLCMD, etc.) that interprets the GO batch terminator commands and sends the preceding batch of SQL statement to the database engine for execution.GO is not a statement but a command to the server to commit the current batch to the Database. It creates a stop inside the current transaction.
You can't use GO inside a stored procedure. If you would try, the definition of the procedure will end there, and the rest will be a separate batch.
A local variable has the scope of the batch, so after a GO command you can't use local variables declared before the GO command:
declare @test int
set @test = 42
GO
select @Test -- causes an error message as @Test is undefined
Comments
GOis not a command. It is an indicator that a batch of commands should run and is not appropriate in a stored procedure.