asked on

Convert this to something usefull (SP or View)

Hello all,

I made some code in SQL, to split a line of text into single fields.
What i need is that I can somehow pass the variable @String into this code, and get back all the fields from the select statment at the bottom.

Can someone guide me how to to this.
I think it needs to be done with Stored Procedures, but I never build 1, so I don't have a clue how.

Thanks in advance

DECLARE @NextString VARCHAR(300)
DECLARE @NextString1 VARCHAR(300)
DECLARE @NextString2 VARCHAR(300)
DECLARE @NextString3 VARCHAR(300)

DECLARE @Pos1 INT
DECLARE @Pos2a INT
DECLARE @Pos2b INT
DECLARE @Pos2c INT
DECLARE @NextPos1 INT
DECLARE @NextPos2 INT
DECLARE @String VARCHAR(300)
DECLARE @String1 VARCHAR(300)
DECLARE @String2 VARCHAR(300)
DECLARE @String3 VARCHAR(300)
DECLARE @Delimiter1 VARCHAR(1)
DECLARE @Delimiter2 VARCHAR(6)
DECLARE @LineCounter int
DECLARE @Counter int
Declare @Size1 varchar(3)
Declare @SizeAssortment1 varchar(3)
Declare @SizeTotals1 varchar(3)

Declare @Size2 varchar(3)
Declare @SizeAssortment2 varchar(3)
Declare @SizeTotals2 varchar(3)

Declare @Size3 varchar(3)
Declare @SizeAssortment3 varchar(3)
Declare @SizeTotals3 varchar(3)

Declare @Size4 varchar(3)
Declare @SizeAssortment4 varchar(3)
Declare @SizeTotals4 varchar(3)

Declare @Size5 varchar(3)
Declare @SizeAssortment5 varchar(3)
Declare @SizeTotals5 varchar(3)

Declare @Size6 varchar(3)
Declare @SizeAssortment6 varchar(3)
Declare @SizeTotals6 varchar(3)

Declare @Size7 varchar(3)
Declare @SizeAssortment7 varchar(3)
Declare @SizeTotals7 varchar(3)

Declare @Size8 varchar(3)
Declare @SizeAssortment8 varchar(3)
Declare @SizeTotals8 varchar(3)

Declare @Size9 varchar(3)
Declare @SizeAssortment9 varchar(3)
Declare @SizeTotals9 varchar(3)

Declare @Size10 varchar(3)
Declare @SizeAssortment10 varchar(3)
Declare @SizeTotals10 varchar(3)

Declare @Size11 varchar(3)
Declare @SizeAssortment11 varchar(3)
Declare @SizeTotals11 varchar(3)

Declare @Size12 varchar(3)
Declare @SizeAssortment12 varchar(3)
Declare @SizeTotals12 varchar(3)


--> @String is de variable die als input wordt meegegeven
SET @String ='10,21,32' + CHAR(10)+CHAR(13) + '9,1,2' + CHAR(10)+CHAR(13) + '25,25,50'
--SET @String = (SELECT TOP 1 SizeRangeAssortment from _TCC_Qu_002) 

--> @Delimiter1 is de variable voor het regeleinde, Delimiter2 is voor de komma
SET @Delimiter1 = CHAR(10)+CHAR(13)
SET @Delimiter2 = ','

--> @Pos1 bepalen de posities voor de regeleindes
SET @Pos1 = charindex(@Delimiter1,@String)

--> @String = zorgen dat er een regeleinde wordt toegevoegd
SET @String = @String + @Delimiter1

--> @LineCounter op 0 zetten om daarmee te kunnen checken welke variable moet worden gevult
SET @lineCounter = 0


--> Aanmaken van @string1,@String2,@String3
WHILE (@Pos1 <> 0)
	BEGIN
	SET @NextString = substring(@String,1,@Pos1 - 1)
	SET @LineCounter = @LineCounter+1
		IF @LineCounter = 1 BEGIN SET @String1 = @NextString END
		IF @LineCounter = 2 BEGIN SET @String2 = @NextString END
		IF @LineCounter = 3 BEGIN SET @String3 = @NextString END
	SET @String = substring(@String,@pos1+1,len(@String))
	SET @pos1 = charindex(@Delimiter1,@String)
	END
	
--Select @String1,@String2 ,@String3 
----> @string1,@String2,@String3 aangemaakt


--> @String1, @String2, @String3 aanvullen met een komma
SET @String1 = @String1 + @Delimiter2
SET @String2 = @String2 + @Delimiter2
SET @String3 = @String3 + @Delimiter2

--> @Pos2 bepalen de posities voor de komma's
SET @Pos2a = charindex(@Delimiter2,@String1)
SET @Pos2b = charindex(@Delimiter2,@String2)
SET @Pos2c = charindex(@Delimiter2,@String3)

--> Aanmaken van de sizes
SET @Counter = 0

		WHILE (@pos2a <> 0)
			BEGIN
			SET @NextString1 = substring(@String1,1,@Pos2a - 1)
			SET @NextString2 = substring(@String2,1,@Pos2b - 1)
			SET @NextString3 = substring(@String3,1,@Pos2c - 1)
			SET @Counter = @Counter+1

			IF @Counter = 1 BEGIN 
				SET @Size1 = @NextString1 
				SET @SizeAssortment1 = @NextString2
				SET @SizeTotals1 = @NextString3 
			END
			IF @Counter = 2 BEGIN 
				SET @Size2 = @NextString1 
				SET @SizeAssortment2 = @NextString2
				SET @SizeTotals2 = @NextString3 
			END
			IF @Counter = 3 BEGIN 
				SET @Size3 = @NextString1 
				SET @SizeAssortment3 = @NextString2
				SET @SizeTotals3 = @NextString3 
			END
			IF @Counter = 4 BEGIN 
				SET @Size4 = @NextString1 
				SET @SizeAssortment4 = @NextString2
				SET @SizeTotals4 = @NextString3 
			END
			IF @Counter = 5 BEGIN 
				SET @Size5 = @NextString1 
				SET @SizeAssortment5 = @NextString2
				SET @SizeTotals5 = @NextString3 
			END
			IF @Counter = 6 BEGIN 
				SET @Size6 = @NextString1 
				SET @SizeAssortment6 = @NextString2
				SET @SizeTotals6 = @NextString3 
			END
			IF @Counter = 7 BEGIN 
				SET @Size7 = @NextString1 
				SET @SizeAssortment7 = @NextString2
				SET @SizeTotals7 = @NextString3 
			END
			IF @Counter = 8 BEGIN 
				SET @Size8 = @NextString1 
				SET @SizeAssortment8 = @NextString2
				SET @SizeTotals8 = @NextString3 
			END
			IF @Counter = 9 BEGIN 
				SET @Size9 = @NextString1 
				SET @SizeAssortment9 = @NextString2
				SET @SizeTotals9 = @NextString3 
			END
			IF @Counter = 10 BEGIN 
				SET @Size10 = @NextString1 
				SET @SizeAssortment10 = @NextString2
				SET @SizeTotals10 = @NextString3 
			END
			IF @Counter = 11 BEGIN 
				SET @Size11 = @NextString1 
				SET @SizeAssortment11 = @NextString2
				SET @SizeTotals11 = @NextString3 
			END
			IF @Counter = 12 BEGIN 
				SET @Size12 = @NextString1 
				SET @SizeAssortment12 = @NextString2
				SET @SizeTotals12 = @NextString3 
			END
			

			SET @String1 = substring(@String1,@pos2a+1,len(@String1))
			SET @pos2a = charindex(@Delimiter2,@String1)
			
			SET @String2 = substring(@String2,@pos2b+1,len(@String2))
			SET @pos2b = charindex(@Delimiter2,@String2)
			
			SET @String3 = substring(@String3,@pos2c+1,len(@String3))
			SET @pos2c = charindex(@Delimiter2,@String3)
		END 

select @Size1 AS Size1, @size2 AS Size2, @Size3 AS Size3, @size4 AS Size4,@Size5 AS Size5, @size6 AS Size6, 
		@Size7 AS Size7, @size8 AS Size8,@Size9 AS Size9, @size10 AS Size10, @Size11 AS Size11, @size12 AS Size12,
		@SizeAssortment1 AS SizeAssortment1,@SizeAssortment2 AS SizeAssortment2,@SizeAssortment3 AS SizeAssortment3,@SizeAssortment4 AS SizeAssortment4,
		@SizeAssortment5 AS SizeAssortment5,@SizeAssortment6 AS SizeAssortment6,@SizeAssortment7 AS SizeAssortment7,@SizeAssortment8 AS SizeAssortment8,
		@SizeAssortment9 AS SizeAssortment9,@SizeAssortment10 AS SizeAssortment10,@SizeAssortment11 AS SizeAssortment11,@SizeAssortment12 AS SizeAssortment12,
		@SizeTotals1 AS SizeTotals1,@SizeTotals2 AS SizeTotals2,@SizeTotals3 AS SizeTotals3,@SizeTotals4 AS SizeTotals4,@SizeTotals5 AS SizeTotals5,
		@SizeTotals6 AS SizeTotals6,@SizeTotals7 AS SizeTotals7,@SizeTotals8 AS SizeTotals8,@SizeTotals9 AS SizeTotals9,@SizeTotals10 AS SizeTotals10,
		@SizeTotals11 AS SizeTotals11,@SizeTotals12 AS SizeTotals12

Open in new window

dqmq

Create Proc YourProcName (@string varchar(max))
as
begin

remove lines 12, 69-71 from your code and put the rest here

end
go

thecookiecompany

ASKER

@dqmq: Thanks for the help.

This i have done, but now i face problem 2.
I'have a View with already a lot of results, and I need to add, all the results from the SP.
How do I call the SP from my existing View, so that it will add these results?

thecookiecompany

ASKER

Extra info:
The result from the view will have more the 1 record, and all of these records, needs to call this SP

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

the code you have looks overly complex could you confirm what you are actually trying to extract...

if you just give an example Input and the Output i can probably work with that..

the view you have ... is that a table of these strings to split or are you trying to apply the output of this string to
the data you have in the view .. ie are you trying to use the string to parse the output of the view....

thecookiecompany

ASKER

Thank, using a table-function work exactly the way i needed it.