How to avoid loops in this recordset [Resolved]

Posted by Self-Innovator under Sql Server on 12/21/2012 | Points: 10 | Views : 1348 | Status : [Member] | Replies : 2
Hi,
In my below sp after executing it..as EXEC sp_SplitVal


ALTER PROC sp_SplitVal
AS
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500)
DECLARE @StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
--SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='4fe6559c-bc93-4d9c-bcb2-013fede7d969')
WHILE @StartingPos<=LEN(@Test)
BEGIN
SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
PRINT @RecSet
BEGIN
INSERT INTO @temptable VALUES(@RecSet)
SELECT * FROM @temptable
SELECT @StartingPos=@RecPos+1
END
END



i am getting the results in a loop for three times..as
COMPLEXIONID
1
COMPLEXIONID
1
3
COMPLEXIONID
1
3
4


but i need it as in a single recordset..
COMPLEXIONID
1
3
4

i dont want in a loop...how to achieve this...

Join Hands Change lives
Thanks & Regards
Straight Edge Society



Responses

Posted by: Self-Innovator on: 12/21/2012 [Member] Bronze | Points: 25

Up
0
Down

Resolved
ALTER PROC sp_SplitVal

AS
BEGIN
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500),@StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
--SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='4fe6559c-bc93-4d9c-bcb2-013fede7d969')
WHILE @StartingPos<=LEN(@Test)
BEGIN
SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
PRINT @RecSet
BEGIN
INSERT INTO @temptable VALUES(@RecSet)
SELECT @StartingPos=@RecPos+1
END
END
SELECT * FROM @temptable
END


EXEC sp_SplitVal

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Webmaster on: 12/21/2012 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Dear Self-Motivator,

Thanks for responding to the post. However as you are the initiator of this post, you are not supposed to mark your post as Answer instead please post the question along with the answer in the Forums under "Error and Resolution" category.

As we are keep getting complaint from some members that such kind of activities are being performed by many members to win the prizes (however we understand that your intention may not be this).

Thanks for understanding.


Best regards,
Webmaster
http://www.dotnetfunda.com

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response