April 13, 2017 at 2:31 pm
Experts,
I have a query that has 3,800 line of sql codes.i created a sp called sp_1 using this code. but the thing is sp only has 2,000 lines of code the rest of them are gone. Do you have any idea why this happened? and how to solve it?
part of the code:
At the beginning-
create PROC SP_1
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Parameters') IS NOT NULL DROP TABLE #Parameters
IF OBJECT_ID('tempdb..#OverreadInitials') IS NOT NULL DROP TABLE #OverreadInitials
IF OBJECT_ID('tempdb..#CursorItems') IS NOT NULL DROP TABLE #CursorItems
IF OBJECT_ID('tempdb..#SupplementalReviewData') IS NOT NULL DROP TABLE #SupplementalReviewData
...
...
In the middile of the query I also have this and
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Parameters') IS NOT NULL DROP TABLE #Parameters
IF OBJECT_ID('tempdb..#OverreadInitials') IS NOT NULL DROP TABLE #OverreadInitials
IF OBJECT_ID('tempdb..#CursorItems') IS NOT NULL DROP TABLE #CursorItems
IF OBJECT_ID('tempdb..#SupplementalReviewData') IS NOT NULL DROP TABLE #SupplementalReviewData
GO
... From this part all the codes are gone in the SP_1
...
...
April 13, 2017 at 2:59 pm
It's because you have a GO statement in your code.
When you try to create the proc, the GO statement tells SQL that you are finished with the proc, and everything after that is another batch of statements.
April 14, 2017 at 3:41 am
GO is not a T-SQL command. It's a Management Studio batch terminator. When SSMS (and several other clients) encounters a GO, it sends the text up to that point to SQL Server as a batch. Anything after that point is sent as a second batch, and since it would have started with some T-SQL statement, SQL Server interpreted it (correctly) as some ad-hoc code to run, which it would have done.
p.s. Don't start procedure names with sp_. It stands for 'system procedure' and means that SQL Server first looks for your procedure's definition in Master and the SystemResourceDatabase before the user database. A meaningful name without a prefix at all is my preference for object names.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply