CODEWITHSUNDEEP
sqlsql-serverstored-procedures
R B
R B

Reputation: 423

Iterate through values of a table variable in a stored procedure

I am writing a stored proc as follows:

 CREATE PROCEDURE ListByOrderRequestId
    @EntityId   int,
    @EntityTypeId varchar(8)    
AS
BEGIN      
    SET NOCOUNT ON;
    Declare @IDS table(OrderRequestId int)
    INSERT INTO @IDS
        SELECT  OrderRequestTaskId       
        FROM OrderRequestTask ORT WITH (NOLOCK)          
        WHERE ORT.OrderRequestId = @EntityId

        SELECT  N.EntityNoteId AS Id, 
                N.UpdateDate AS DateStamp, 
                N.EntityNoteText,                   
                N.EntityId,
                N.EntityNoteTypeId,
                N.EntityTypeId
        FROM  EntityNote N          
        WHERE (N.EntityId = @EntityId AND N.EntityTypeId ='ORDREQ')
              *OR(N.EntityId = @IDS VAL1 AND N.EntityTypeId ='TASK')  
               OR(N.EntityId = @IDS VAL2 AND N.EntityTypeId ='TASK')*                   


END

The table @IDS can have 0 or 1 or more values in it. I want to loop through the values in @TDS and create the where clause above accordingly. Please help me.

Upvotes: 0

Views: 1136

Answers (1)

Steve
Steve

Reputation: 5545

As opposed to looping through the table, you could just use it in your where clause like this:

Select * From {Your Table} Where ID in (Select OrderRequestId From IDS)

This is much faster than looping.

Upvotes: 2

Related Questions