SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 loop through lines from a stored proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eric.bryan
Starting Member

1 Posts

Posted - 08/20/2013 :  08:28:55  Show Profile  Reply with Quote
Hello everybody,

I have created a stored proc to loop through all lines in another stored proc.

The stored proc executes but exits the loop randomly before reaching the last line of the parsed stored proc.

There is no error message, the looping stored proc cannot loop until the end of the parsed stored proc.

Could you help me understand the problem ?

Thank you very much in advance.

Here are the code of the main proc, the parsed proc and the table which is used by the parsed proc.

Main proc :

CREATE PROCEDURE [dbo].[P_TEST_HEADER_PROC_2] 
(
@SpBody		TEXT = null
)
AS

BEGIN
	DECLARE @NewLine VARCHAR(2) = CHAR(13) + CHAR(10);
	DECLARE @CurrPos BIGINT = 0;
	DECLARE @PosNewLine BIGINT;
	DECLARE @Length BIGINT;
	DECLARE @LineContent VARCHAR(Max);
	DECLARE @ReturnedString VARCHAR(Max) = '';
	DECLARE @CurrentPosTotal BIGINT = 0;
	DECLARE @RowIndex BIGINT = 0;
	
	--Get the stored proc body	
	SELECT @SpBody = definition
	FROM sys.sql_modules
	WHERE object_id = (OBJECT_ID(N'dbo.PROC_1'));

	SET @Length = DATALENGTH(@SpBody);
	
	SELECT 'Length : ' + CONVERT(Varchar, @Length);
	
	WHILE @CurrentPosTotal <= @Length
	BEGIN
		
		--get the remaining text from the sp body
		SET @SpBody = SUBSTRING(@SpBody, @CurrPos, DATALENGTH(@SpBody));
		SELECT '@SpBody : ';
		SELECT @SpBody;
		
		-- get the next new line position
		SET @PosNewLine = PATINDEX('%' + @NewLine + '%',@SpBody);
		
		SET @RowIndex = @RowIndex + 1
		
		--if there is no remaining new line
		IF @PosNewLine = 0
		BEGIN
			SELECT 'End of program';
			BREAK;
		END 
		
		SELECT '@PosNewLine : ' + CONVERT(Varchar, @PosNewLine);
		
		--get the text between the last new line and the next new line
		SET @LineContent = SUBSTRING(@SpBody, 0, @PosNewLine);
		SELECT '@LineContent : ' + CONVERT(Varchar, @LineContent);			
		
		--the current position is the next new line
		SET @CurrPos = @PosNewLine + 1;
		
		SELECT '@CurrPos : ' + CONVERT(Varchar, @CurrPos);
		
		SELECT '@RowIndex : ' + CONVERT(Varchar, @RowIndex);
		--test 
		SET @ReturnedString = @ReturnedString + '@@@@' + @LineContent;
		SELECT '@ReturnedString : ' + @ReturnedString;	
		
		SET @CurrentPosTotal = @CurrentPosTotal + @CurrPos;
	END
	-- Return the result of the function
	
	SELECT 'End @ReturnedString';
	SELECT @ReturnedString;

END


parsed proc :

CREATE PROCEDURE [dbo].[PROC_1] 
(
	@FIELD_1	BIGINT OUTPUT, 
	@FIELD_2	VARCHAR(8),
	@FIELD_3	VARCHAR(8),
	@FIELD_4	VARCHAR(8),
	@FIELD_5	VARCHAR(8),
	@FIELD_6	VARCHAR(8),
	@FIELD_7	CHAR(50),
	@FIELD_8	VARCHAR(8),
	@FIELD_9	DECIMAL(10, 2),
	@FIELD_10	VARCHAR(100),
	@FIELD_11	VARCHAR(100),
	@FIELD_12	VARCHAR(100),
	@FIELD_14	VARCHAR(100),
	@FIELD_15	VARCHAR(100),
	@FIELD_16	VARCHAR(100),
	@FIELD_17	VARCHAR(10),
	@FIELD_18	VARCHAR(100),
	@FIELD_19	DATETIME,
	@FIELD_20	VARCHAR(8),
	@FIELD_21	VARCHAR(8),
	@FIELD_22	DATETIME,
	@FIELD_23	SMALLINT,
	@FIELD_24	SMALLINT,
	@FIELD_25	VARCHAR(11),
	@FIELD_26	VARCHAR(34),
	@FIELD_27	VARCHAR(50),
	@FIELD_28	BIGINT,
	@FIELD_29	VARCHAR(50),
	@FIELD_30	VARCHAR(3),
	@FIELD_31	DATETIME,
	@FIELD_32	DATETIME,
	@FIELD_33	VARCHAR(3),
	@FIELD_34	VARCHAR(8),
	@FIELD_35	VARCHAR(20),
	@FIELD_36	VARCHAR(100),
	@FIELD_37	BIGINT,
    @FIELD_38	DATETIME,
    @FIELD_39	VARCHAR(100),
    @FIELD_40	DATETIME
)
AS
BEGIN

	INSERT INTO T_TABLE1 (         
		FIELD_2,
		FIELD_3,
		FIELD_4,
		FIELD_5,
		FIELD_6,
		FIELD_7,
		FIELD_8,
		FIELD_9,
		FIELD_10,
		FIELD_11,
		FIELD_12,
		FIELD_14,
		FIELD_15,
		FIELD_16,
		FIELD_17,
		FIELD_18,
		FIELD_19,
		FIELD_20,
		FIELD_21,
		FIELD_22,
		FIELD_23,
		FIELD_24,
		FIELD_25,
		FIELD_26,
		FIELD_27,
		FIELD_28,
		FIELD_29,
		FIELD_30,
		FIELD_31,
		FIELD_32,
		FIELD_33,
		FIELD_34,
		FIELD_35,
		FIELD_36,
		FIELD_37,
		FIELD_38,
		FIELD_39,
		FIELD_40)
	VALUES(
		@FIELD_2,
		@FIELD_3,
		@FIELD_4,
		@FIELD_5,
		@FIELD_6,
		@FIELD_7,
		@FIELD_8,
		@FIELD_9,
		@FIELD_10,
		@FIELD_11,
		@FIELD_12,
		@FIELD_14,
		@FIELD_15,
		@FIELD_16,
		@FIELD_17,
		@FIELD_18,
		@FIELD_19,
		@FIELD_20,
		@FIELD_21,
		@FIELD_22,
		@FIELD_23,
		@FIELD_24,
		@FIELD_25,
		@FIELD_26,
		@FIELD_27,
		@FIELD_28,
		@FIELD_29,
		@FIELD_30,
		@FIELD_31,
		@FIELD_32,
		@FIELD_33,
		@FIELD_34,
		@FIELD_35,
		@FIELD_36,
		@FIELD_37,
		@FIELD_38,
		@FIELD_39,
		@FIELD_40
	)

	SET @FIELD_1 = SCOPE_IDENTITY();

END


table used by the parsed proc :

CREATE TABLE [dbo].[T_TABLE1](
	[FIELD_1] [bigint] NULL,
	[FIELD_2] [varchar](8) NULL,
	[FIELD_3] [varchar](8) NULL,
	[FIELD_4] [varchar](8) NULL,
	[FIELD_5] [varchar](8) NULL,
	[FIELD_6] [varchar](8) NULL,
	[FIELD_7] [char](50) NULL,
	[FIELD_8] [varchar](8) NULL,
	[FIELD_9] [decimal](10, 2) NULL,
	[FIELD_10] [varchar](100) NULL,
	[FIELD_11] [varchar](100) NULL,
	[FIELD_12] [varchar](100) NULL,
	[FIELD_14] [varchar](100) NULL,
	[FIELD_15] [varchar](100) NULL,
	[FIELD_16] [varchar](100) NULL,
	[FIELD_17] [varchar](10) NULL,
	[FIELD_18] [varchar](100) NULL,
	[FIELD_19] [datetime] NULL,
	[FIELD_20] [varchar](8) NULL,
	[FIELD_21] [varchar](8) NULL,
	[FIELD_22] [datetime] NULL,
	[FIELD_23] [smallint] NULL,
	[FIELD_24] [smallint] NULL,
	[FIELD_25] [varchar](11) NULL,
	[FIELD_26] [varchar](34) NULL,
	[FIELD_27] [varchar](50) NULL,
	[FIELD_28] [bigint] NULL,
	[FIELD_29] [varchar](50) NULL,
	[FIELD_30] [varchar](3) NULL,
	[FIELD_31] [datetime] NULL,
	[FIELD_32] [datetime] NULL,
	[FIELD_33] [varchar](3) NULL,
	[FIELD_34] [varchar](8) NULL,
	[FIELD_35] [varchar](20) NULL,
	[FIELD_36] [varchar](100) NULL,
	[FIELD_37] [bigint] NULL,
	[FIELD_38] [datetime] NULL,
	[FIELD_39] [varchar](100) NULL,
	[FIELD_40] [datetime] NULL
) ON [PRIMARY]

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 08/20/2013 :  11:08:00  Show Profile  Reply with Quote
I can assure you that SQL Server would not exit randomly.

Maybe you could have a counter to see how many times it goes through the loop? Or do a SQL Profiler trace and trace every statement.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
383 Posts

Posted - 08/20/2013 :  15:49:43  Show Profile  Reply with Quote
First, I'd change the data type for
@SpBody
from TEXT (yikes!) to nvarchar(max) or even varchar(max).

I'm not sure a conversion to TEXT will retain the entire procedure code.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000