March 5, 2012 at 8:34 am
Hi there,
I was wondering if you could nestle GO statements inside of a BEGIN END statement when creating a new procedure
ie.
CREATE PROCEDURE [dbo].[test]
AS
USE mydatabase
BEGIN
GO
INSERT INTO [tbl2] ([id], [name])
SELECT [id], [name]
FROM [tbl1]
GO
DELETE FROM [tbl4]
INSERT INTO [tbl4] ([description], [owner])
SELECT [description], [owner]
FROM [tbl3]
GO
END
March 5, 2012 at 8:42 am
You can't have a USE in the procedure, and the first GO will be the end of the procedure.
GO is not a T-SQL command. It's a client tool batch-breaker. It marks the end of a set of commands sent to SQL Server
CREATE PROCEDURE [dbo].[test]
AS
INSERT INTO mydatabase..[tbl2] ([id], [name])
SELECT [id], [name]
FROM mydatabase..[tbl1]
DELETE FROM mydatabase..[tbl4]
INSERT INTO mydatabase..[tbl4] ([description], [owner])
SELECT [description], [owner]
FROM mydatabase..[tbl3]
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2012 at 8:45 am
Did you try it??? Of course it won't work. There are a number of problems with that, not the least of which is you can't have a use statement in a proc. Then you have the issue of not ending your begin.
It seems that you are trying to create a generic proc that will insert and delete from another database??
The whole way you your batches is just not going to work.
You could however do something like this.
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
INSERT INTO mydatabase.myschema.[tbl2] ([id], [name])
SELECT [id], [name]
FROM mydatabase.myschema.[tbl1]
DELETE FROM mydatabase.myschema.[tbl4]
INSERT INTO mydatabase.myschema.[tbl4] ([description], [owner])
SELECT [description], [owner]
FROM mydatabase.myschema.[tbl3]
END
--once again Gail was typing (and faster) a response at the same time I was.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 10:12 am
I wanted to know if it was possible to subdivide the statements into separate scripts. But it sounds like it's not possible with the GO statement since it would end the procedure. Is there another way to do this?
March 5, 2012 at 10:16 am
What do you mean by 'separate scripts'? What's the goal here, what are you trying to achieve?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2012 at 10:18 am
guerillaunit (3/5/2012)
I wanted to know if it was possible to subdivide the statements into separate scripts. But it sounds like it's not possible with the GO statement since it would end the procedure.
The GO does exactly this: subdivides the script into separate batches.
But, it's not possible within stored procedure as it's not a script - it is an object.
Is there another way to do this?
To do what?
March 5, 2012 at 1:02 pm
Hi Eugene,
I'd like to divide the statement into separate subscripts because the subscripts need to be processed in sequence. I was opening to use the GO command so that SQL doesn't try to evaluate the code as 1 sequence, but rather as a series of separate sequences
March 5, 2012 at 1:05 pm
guerillaunit (3/5/2012)
Hi Eugene,I'd like to divide the statement into separate subscripts because the subscripts need to be processed in sequence. I was opening to use the GO command so that SQL doesn't try to evaluate the code as 1 sequence, but rather as a series of separate sequences
HUH?
A stored procedure is a block of code. What do you mean by subscripts and breaking it apart? What do you mean by sequences? A more clear explanation of what you are trying to accomplish may shed light on a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 1:45 pm
guerillaunit (3/5/2012)
I'd like to divide the statement into separate subscripts because the subscripts need to be processed in sequence.
Not following.
If you have a set of statements (like in the example procedure that I wrote earlier), the statements will be executed one by one in sequence.
So if we have this:
CREATE PROCEDURE [dbo].[test]
AS
INSERT INTO mydatabase..[tbl2] ([id], [name])
SELECT [id], [name]
FROM mydatabase..[tbl1]
DELETE FROM mydatabase..[tbl4]
INSERT INTO mydatabase..[tbl4] ([description], [owner])
SELECT [description], [owner]
FROM mydatabase..[tbl3]
and that procedure is executed then first the insert into tbl2 will run. Once complete the delete from tbl4 will run. Once that's complete the insert into tbl4 runs. Once that finishes the procedure is complete.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2012 at 3:06 pm
guerillaunit (3/5/2012)
Hi Eugene,I'd like to divide the statement into separate subscripts because the subscripts need to be processed in sequence. I was opening to use the GO command so that SQL doesn't try to evaluate the code as 1 sequence, but rather as a series of separate sequences
As Gail pointed out, SQL runs in sequence; i.e. Top down. It is a scripting language that executes from beginning to end, not all at once. SQL is not OO and does not contain modules or objects in the same way that C# does.
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply