We help IT Professionals succeed at work.

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

Comment
Watch Question

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

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


end
go

Author

Commented:
@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?

Author

Commented:
Extra info:
The result from the view will have more the 1 record, and all of these records, needs to call this SP
Commented:
You can't use an SP that way.  Instead you need to create a temp table, insert records to it using the SP and then join to that.

Perhaps a table-valued function is better suited to your needs.

Create function dbo.YourFunctionName (@string varchar(max))
returns @tableout table(size1 varchar(10), size2 varchar(10), etc)
as

begin

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

add this line just before the final select

Insert into @tableout

end



Then you can join your view to the function or use the function within the view.

Select * from yourview, dbo.Yourfunction('input string goes here')
Top Expert 2011

Commented:
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....

Author

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