Post reply

Stored procedure with up to 1000 parameters

  • August 8, 2006 at 1:28 pm

    #173762

    Hi there,

    We need to create an stored procedure that handles up to 1000 parameters. In order to work with them, the procedure first insert them in a temporal tamble.

    The point is that we need a way to insert them without creating up to 1000 insert lines at the top of the procedure.

    insert into #TablaParametros values (par1, val1)

    insert into #TablaParametros values (par2, val2)

    insert into #TablaParametros values (par3, val3)

    ...

    We were looking for some reduced code solution (like using a cursor that creates dynamic sql). In fact, we already tried but, when we used exec (<varchar with sql code&gt we found out that variables are not reachable, since exec creates its new context.

    I don´t know if the problem is clear. My english is no good (please ask me anything if you didnt understand)

    Little mess, anyway. Any idea?

    Thanks,

  • August 8, 2006 at 2:28 pm

    #653928

    1000 parameters

    Are you sure that you have no other options but to pass in 1000 parameters?

    I believe passing such a huge number of parameters will create headaches when you try to maintain/debug the code

    Can you give us the scenario for this stored procedure?

    Cheers!

  • August 8, 2006 at 2:42 pm

    #653932

    If you are looking at dynamic sql and accessing variable, look at using the extend stored procedure sp_executesql.  You can find information about that procedure in BOL and I believe there are articles and/or posts on this site as well.

    hth some,

    Lynn

     

  • August 9, 2006 at 3:55 am

    #654010

    I didn´t like it at the beginning... ;-P

    The point is that all 1000 parameters (o 100, whatever) will admit NULL. They are optional. The sp is going to create reports and needs to handle parameters (up to 100, at least).

    Since it will be called from a lot of different places, I don´t want to force every single process that will call the new sp to insert their parameters in a temporal table...

    Bye!

     

  • August 9, 2006 at 4:48 am

    #654021

    Lynn,

    I think the option you talk about would be the most appropiate. I've working around with it, but I end in the same point. Here is the code I run inside a cursor (@count goes from 1 to 1000,...) I don´t get how to send a different variable for each execution.

    Can you help me with this?

    Thanks,

    Gufix

    Select @sql = N'insert into #Parametros (NombreParametro, ValorParametro)' +

    ' values (''Parametro' + cast (@cont as varchar(10))

    + ''', @Parametro)'

    SET @DefPar = N'@Parametro varchar(1000)'

    EXECUTE sp_executesql

    @stmt

    = @sql,

    @params

    = @DefPar,

    @Parametro

    = @parametro + @cont ?????

  • August 10, 2006 at 6:26 am

    #654278

    Have you considered passing in XML and then using the SQL XML functions to create the temp table, or better yet a table variable? 

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 6 posts - 1 through 5 (of 5 total)

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