SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    (MSSQL) - Storing a query result in a variable with a WHILE LOOP

    Hi all,

    I am currently using a cursor to in which to LOOP around a result set and whilst within the LOOP I would like run another query where I need to store the result into a varaible. I have tried the following but that just throws errors at me

    Code:
    @testvar = (SELECT upc_pk FROM lupc_pts_contract WHERE lupc_code = @contrcode)
    I heard its bad to use a cursor within another cursor, so will not attempt this.

    Is there anyway I can get the result from this query into a variable?

    My overall code looks like this...

    Code:
    -- DECLARE VARIABLES HERE
    
    DECLARE c1 CURSOR FOR (SELECT id, plan_date, contract, cancout FROM meridian.dbo.planpatients WHERE LEN(cancout) > 0)
    OPEN c1
    FETCH NEXT FROM c1 INTO @var1, @var2, @var4, @var3
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	-- GET NEW CONTRACT CODE
    	@testvar = (SELECT upc_pk FROM lupc_pts_contract WHERE lupc_code = @contrcode)
    	-- PERFORM CODE EXECUTION HERE!!
    
    FETCH NEXT FROM c1 INTO @var1, @var2, @var4, @var3
    END
    
    CLOSE c1
    DEALLOCATE c1
    Tryst

  2. #2
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stupid me, I should be assigning values to variables by doing the following...

    SET @testvar = QUERY TO BE PERFORMED

    Soz

    Tryst


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •