How to avoid loops in this recordset [Resolved]

Posted by Self-Innovator under Sql Server on 12/21/2012 | Points: 10 | Views : 2168 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

Latest Posts