CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2179026
user2179026

Reputation:

How to fetch data from table using while loop?

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!!!

Upvotes: 0

Views: 4155

Answers (2)

Jodrell
Jodrell

Reputation: 35716

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];

Upvotes: 0

Bernd Linde
Bernd Linde

Reputation: 2152

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.

Upvotes: 1

Related Questions