Click here to Skip to main content
14,331,485 members
Rate this:
Please Sign up or sign in to vote.
Hi all,
When i ran this query i get result as 2 and 5 and two individual rows....wher i have the values 2,5, in my column of my table...
[CODE]DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

DECLARE @TEMPEXPECTATION TABLE(COMPLEXIONID INT)

SET @StartingPos= 1

SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='be65cb50-cc1d-47a5-a3a2-657fae4e6519')

WHILE @StartingPos<=LEN(@Test)

BEGIN

    SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)

    SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)

    PRINT @RecSet

    --INSERT INTO @TEMPEXPECTATION(@ID)


    SELECT @StartingPos=@RecPos+1

END
[/CODE]
Now i need to insert this values 2 and 5 in a temporary table using the above query to made as Stored Procedure...Pls find the solution...
Posted
Comments
arindamrudra 21-Dec-12 0:03am
   
With in that While loop you want to insert? What are the column names or variable names that you want to insert in the temporary table.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Create TABLE #tempdata (ID INT IDENTITY(1,1), FirstValue INT, LastValue INT)
INSERT INTO #tempdata (FirstValue,LastValue) VALUES (@RecPos,@RecSet)
   
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi..while run this query i am getting the values as 2,5, then by using some splitting mechanism i am splitting the values as 2 and 5 respectively now i need to insert these two values in a temp table with column as complexionid...i hope it's clear arindamurdra...
select cmp.complexion1 from customerexpectation cmp where cmp.customerid='be65cb50-cc1d-47a5-a3a2-657fae4e6519'
   
Comments
arindamrudra 21-Dec-12 0:31am
   
Your query will return only one value either 2 or 5. You are missing some thing.
sahmed4 21-Dec-12 0:38am
   
see this below updated query
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

SET @StartingPos= 1

SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='68572b93-16bd-4764-b75f-3aad35fef04b')

WHILE @StartingPos<=LEN(@Test)

BEGIN

SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)

SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)

SET @ID=@RecSet
PRINT @ID

-- SELECT CC.CUSTOMERID,CC.COMPLEXION1,CM.ID FROM CUSTOMEREXPECTATION CC
-- LEFT OUTER JOIN
-- COMPLEXION CM
-- ON CC.COMPLEXION1=@ID WHERE CUSTOMERID='68572b93-16bd-4764-b75f-3aad35fef04b'
--
SELECT @StartingPos=@RecPos+1

END

and the Variable @Test consists of values 2,5, and im getting result in Print @ID statemant as 2 and 5 in two indiviual rows..now i need to insert these values in temp table..pls find soln...i hope now my req is clear..
arindamrudra 21-Dec-12 1:38am
   
Please check my solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100