0

I have written a stored procedure in a loop in ms sql server 2008 something like this

BEGIN TRANSACTION
WHILE(@first <= @last)  
BEGIN

Select @LineOfAuthorityNameSubString = TempLineOfAuthority from #tbTempLineOfAuthority;

Select @tbLineOfAuthorityId = LineOfAuthority
from tbLineOfAuthority where LineOfAuthorityX = @LineOfAuthorityNameSubString;

INSERT INTO tbProductLineOfAuthority(ProductId, LineOfAuthortyId)   
VALUES(@tbProductId, @tbLineOfAuthorityId);

SET @first += @step  
END
COMMIT TRANSACTION

Now the issue is in this line of code

  Select @tbLineOfAuthorityId = LineOfAuthority
  from tbLineOfAuthority where LineOfAuthorityX = @LineOfAuthorityNameSubString;

The variable @tbLineOfAuthorityId is getting the same value at all the time in loop. Please help me!!!

Improve this question
5
  • 3
    Why u use Loop here? Just use one select with insert and thats it. Commented Nov 6, 2014 at 9:04
  • Your code will insert the same @tbProductId and @tbLindeOfAuthorityId for each loop of your loop, is this the intended behaviour? Commented Nov 6, 2014 at 9:19
  • @first, @last and @step are not used in the body of the loop, they can only make the body run repeatedly. Nothing in the loop body effects the value @tbLineOfAuthorityId will be be set to, so it will always be the same value. Commented Nov 6, 2014 at 9:42
  • If your question is not "How to fetch data from a table in loop?", you should ask a different question, asking for the answer you actually want. It is unclear to me what you are asking. Commented Nov 6, 2014 at 9:46
  • If you take the time to read, vote and comment on the answers, we all may learn something about your actual question. However, you could ignore out efforts and harden us to future "Please help me!!!"s. Commented Nov 6, 2014 at 10:12

2 Answers 2

Reset to default
1

The reason why you are getting the same value for @tbLineOfAuthorityId the whole time is because nothing changes inside your first select in your loop, meaning you will always get the same result into the @tbLineOfAuthorityId variable each time the loop runs.

As said in the comments section by others, unless there is a requirement as to why you are using @first, @last and @step that you haven't mentioned, your entire code snippet can be replaced by a single insert statement:

insert into tbProductLineOfAuthority
     ( ProductId,
       LineOfAuthortyId )
select @tbProductId,
       loa.LineOfAuthority
  from tbLineOfAuthority loa
         join
       #tbTempLineOfAuthority tla on tla.TempLineOfAuthority = loa.LineOfAuthorityX

That statement will insert all the values as required (according to the current question and assumption that there is no additional meaning to the usage of @step.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 I independently wrote and posted your answer, then noticed the duplicity.
0

If you want to fetch data from a table using a loop, use a CURSOR.

I'll explain in more detail in a moment but, first consider,

most cursor based approaches can rewritten more efficiently using sets.

If you can write set based queries, using SELECT and INSERT for instance, this will very likely be a better option.

For an example of a set based approach to your specific problem, see Bernd Linde's answer. The canonical answer to your stated question follows.

If you do however need a CURSOR, here are a few guidelines,

Since you will use the data in a read-only fashion and scan through from start to finish, you can make that explicit in the definition. Since the cursor will be READ ONLY and FORWARD_ONLY use the FAST_FORWARD argument, this makes a big difference to performance.

The cursor will be used only for this batch so should be declared with the LOCAL argument.

The code should look something like this

-- Declare the cursor.
DECLARE [someName] CURSOR LOCAL
        FAST FORWARD
FOR
    SELECT
            [Some],
            [Other]
        FROM
            [SomeTable];

OPEN [someName];
BEGIN TRY
    -- Declare variables for storing the row.
    DECLARE @some INT;
    DECLARE @other NVARCHAR(MAX);

    -- Get the first row
    FETCH NEXT FROM [someName] INT @some, @other;

    -- Loop through all the rows
    WHILE @@FTECH_STATUS = 0 BEGIN
        -- Do something useful with @some and @other

        -- Get the next row.
        FETCH NEXT FROM [someName] INT @some, @other;
    END

END TRY
BEGIN CATCH
    -- Error Handling
END CATCH

-- Always close and de-allocate the cursor, once it is opened.
CLOSE [someName];
DEALLOCATE [someName];
Improve this answer

1 Comment

I am not getting the above code... Can you please provide me an example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.