Post reply

sp to get parameters from tables records.

  • March 22, 2007 at 4:25 am

    #123251

    I have a sp which accepts 7 parameters.
     
    I have decided the best/easiest way to store these parameters is as records in a table.
     
     
    How to I get the sp to run for each record in the table - taking field values as the parameters?
     
    Thanks

  • March 22, 2007 at 5:33 am

    #696328

    Hi...

    You can achieve this by using the cursor. You can populate the value of the records in the table into the cursor and iterate through all the records in the stored procedure. The stored procedure will execute for all the records in the table and you can get the desired format

    For Eg

    Your table is ParamTable(Param1,Param2)

    Your Procedure Name is sproc_paramTable

    the procedure goes like this

    CREATE PROCEDURE sproc_paramTable

    AS

    DECLARE @Param1 varchar(10), @Param2 varchar(10)

    DECLARE C1 Cursor FOR SELECT Param1,Param2 FROM ParamTable

    OPEN C1

    FETCH Next FROM C1 into @Param1,@Param2

    WHILE @@Fetch_Status = 0

    BEGIN

           Print @Param1 + Param2 + ' for Processing

    FETCH Next FROM C1 into @Param1,@Param2

    END

    CLOSE C1

    DEALLOCATE C1

    Amit Tiwari
    Software Engineer
    Westbase Technology Pvt Ltd
    Pune

  • March 22, 2007 at 6:13 am

    #696335

    Thankyou very much - that works perfectly.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply