none
Problem with cycle

    Question

  • Hi friends,
    I prepared this procedure, but it runs to infinity...
    Could you help me, please,
    Anna

    DECLARE @COUNTER INT, @NAME CHAR(20)
    SET @COUNTER = 0
    BEGIN
    SET  @COUNTER = @COUNTER+1
    SELECT EmployeeID, LastName FROM dbo.Employees WHERE  EmployeeID= @COUNTER AND  @NAME=LastName
    WHILE @COUNTER<4
    PRINT 'ACTUAL LASTNAME IS:'+ @NAME
    END

    Sunday, October 20, 2013 9:30 AM

All replies

  • Hi,

    What  do you actually want to retrieve from db table?

    Sunday, October 20, 2013 10:03 AM
  • WHILE executes the statement that follows.

    In this case, the statement that follows is the PRINT command. So it will keep printing until @counter reaches 4, which will never happen.


    Gert-Jan

    Sunday, October 20, 2013 10:18 AM
  • this part "SET  @COUNTER = @COUNTER+1"

    should be in the loop (after the while and before the end)


    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 10:32 AM
  • It should be (i don't have MMSM to try..):

    DECLARE @COUNTER INT, @NAME CHAR(20)
    SET @COUNTER = 0

    WHILE @COUNTER<4

    BEGIN

    SELECT EmployeeID, LastName FROM dbo.Employees WHERE  EmployeeID= @COUNTER AND  @NAME=LastName

    PRINT 'ACTUAL LASTNAME IS:'+ @NAME

    SET  @COUNTER = @COUNTER+1

    END

    Sunday, October 20, 2013 10:44 AM
  • Hi Shweta,

    I would like to print 4 surnames (step by step) from table Northwind.Employes wih using of:
    PRINT 'ACTUAL LASTNAME IS:'+ @NAME
    Thanks,

    Anna


    • Edited by Anna1313 Sunday, October 20, 2013 1:22 PM
    Sunday, October 20, 2013 1:07 PM
  • Thanks DIEGOCTN,

    I tried it, but the result were 4 emty tables, which was created with using of: SELECT EmployeeID, LastName FROM dbo.Employees WHERE  EmployeeID= @COUNTER AND  @NAME=LastName.
    EmployeeID in the table are: 1,2,3,4,...

    Anna



    • Edited by Anna1313 Sunday, October 20, 2013 1:21 PM
    Sunday, October 20, 2013 1:11 PM
  • Sorry pituach,
    I am not Jewess.
    Anna
    • Edited by Anna1313 Sunday, October 20, 2013 1:23 PM
    Sunday, October 20, 2013 1:13 PM
  • WHAT ?!?

    WHAT DO I CARE WTF ARE YOU???

    i gave you the answer (same answer basically that DIEGOCTN post after i did). this is your mistake!

    you put the command "SET @COUNTER = @COUNTER+1" outside the loop, therefor the lopp never ended! it is very simple. and again WTF do i care if you are Jewess or not and what it has to do with the forum?!?


    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 1:32 PM
  • Sorry pituach,
    I am not Jewess.
    Anna
    May you explain your meaning, please? I hope it because you have confused the disclaimer of pituach (Forum guideline for proper usage) with an answer you weren't able to read...if is not please, give us some explaination... 
    Sunday, October 20, 2013 2:31 PM
  • Sorry pituach, ) :
    I thought of you sending me some instructions...  I certainly did not want to be impolite.
    Thanks for advice, despite of my script don't work corectly,
    Anna

    Sunday, October 20, 2013 2:32 PM
  • I got SSMS now...that's works perfectly for you:

    create table #forum (empl int, lastname char(20))
    insert into #forum values (1,'pop'), (2,'nih'), (3,'pli'),(4,'fre')
    DECLARE @COUNTER INT, @NAME CHAR(20)
    SET @COUNTER = 1
    WHILE @COUNTER<5
    BEGIN
    with cte as(
    SELECT Empl, LastName FROM #forum WHERE  Empl= @COUNTER )
    select @name=lastname from cte
    
    PRINT 'ACTUAL LASTNAME IS:'+ @NAME
    
    SET  @COUNTER = @COUNTER+1
    
    END

    Please vote if this post helped you

    Sunday, October 20, 2013 2:50 PM
  • In simple worlds the vast concept of theory as we know from any past programming concepts that

    syntax of while loop

    while(condition)

    {

    statements

    }

    so we can set value before while, and run the loop with condition passing statements while true, with some increment/decrement or break status for stop infinity.

    So I think the code will be like this,

    DECLARE @COUNTER INT, @NAME CHAR(20)

    SET @COUNTER = 0

    WHILE @COUNTER<4

    BEGIN
    SELECT EmployeeID, LastName FROM dbo.Employees WHERE  EmployeeID= @COUNTER AND  @NAME=LastName

    PRINT 'ACTUAL LASTNAME IS:'+ @NAME

    SET  @COUNTER = @COUNTER+1

    IF @COUNTER>3

    break

    END



    Sunday, October 20, 2013 3:15 PM
  • Nice theory  but your code has some mistake:

    SET @COUNTER = 0 ....... SET @COUNTER = 1

    WHILE @COUNTER<4 ..... WHILE @COUNTER<5

    SELECT EmployeeID, LastName FROM dbo.Employees WHERE  EmployeeID= @COUNTER AND  @NAME=LastName (that's not a conditon...)

    and besides 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.'

    Sunday, October 20, 2013 3:23 PM
  • Thanks DIEGOCTN, ( :
    it works good. But what does it mean cte in this procedure? And how I can prepare procedure, if Empl were created randomly?

    Anna


    • Edited by Anna1313 Sunday, October 20, 2013 4:38 PM
    Sunday, October 20, 2013 4:26 PM
  • CTE = Common Table Expressions

    * You can use any name that you want for CTE table (I recommend not to use the name CTE for CTE like not using the name table for a table... you can use "with MyCTE" for example or any name that you want).

    You can read more about using CTE here:
    http://www.4guysfromrolla.com/webtech/071906-1.shtml

    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 5:17 PM
  • you can do the same without CTE like this (base on the query above):

    -- let's create the temporary table and insert some value (this is DDL+DML)
    create table #forum (empl int, lastname char(20))
    insert into #forum values (1,'pop'), (2,'nih'), (3,'pli'),(4,'fre')
    
    -- This is DIEGOCTN's Query with CTE
    DECLARE @COUNTER INT, @NAME CHAR(20)
    SET @COUNTER = 1
    WHILE @COUNTER<5
    BEGIN
    	with cte as(
    		SELECT Empl, LastName FROM #forum WHERE  Empl= @COUNTER
    	)
    	select @name=lastname from cte
    	PRINT 'ACTUAL LASTNAME IS:'+ @NAME
    	SET  @COUNTER = @COUNTER+1
    END
    GO
    
    -- This is the same without CTE
    DECLARE @COUNTER INT, @NAME CHAR(20)
    SET @COUNTER = 1
    WHILE @COUNTER<5
    BEGIN
    	SELECT @NAME = LastName FROM #forum WHERE Empl= @COUNTER
    	PRINT 'ACTUAL LASTNAME IS:'+ @NAME
    	SET  @COUNTER = @COUNTER+1
    END
    GO


    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 5:26 PM
  • Thanks pituach, ( :
    It looks good. But how I can prepare procedure, if Empl number were created randomly? I would like to take rows step by step from first to last.
    Thanks for advices,
    Anna

    Monday, October 21, 2013 4:40 AM