loop through lines from a stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

loop through lines from a stored proc

Author

Topic

eric.bryan
Starting Member

1 Post

Posted - 2013-08-20 : 08:28:55

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

596 Posts

Posted - 2013-08-20 : 11:08:00

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.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-20 : 15:49:43

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources