Change dynamically number of arguments

Posted on 2011-04-19
Last Modified: 2012-05-11
Hello EE,

In my code I call .sql wich in it is like the following:

exec myStoreProcedure
       @Val = '{0}'
     , @Val2 = '{1}'
     , @Val3 = '{2}'

and so on if there is more parameter.

But my problem is that it could change depending of what the user is doing.
I need sometimes to call it and there will be only 2 args... but sometimes 10
So i just cant hardcode the arguments...nor in the vb file nor in the .sql (well i think?)

what should I do ?
Question by:PhilippeRenaud
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    could you work with a list of values for the same stuff?

    note that you could defined parameters in a stored procedure to be optional:
    create procedure demo_proc @arg1 int , @arg2 int, @arg3 int = null, @arg4 int = -1
    select @arg1, @arg2, @arg3, @arg4 

    Open in new window

    and try it out:
    exec demo_proc 1, 2
    exec demo_proc 1, 2, 3
    exec demo_proc 1, 2 , 3, 4
    -- or even with naming the parameters:
    exec demo_proc @arg3 = 1, @arg4 = 2, @arg2 = 3, @arg1 = 4

    Open in new window

    hope this helps
    LVL 39

    Accepted Solution

    you can change the store procedure to accetp optiona lparameter
    someting like tghis example

        @Param1 varchar(50) = NULL,
        @Param2 varchar(50) = NULL,
        @Param3 varchar(50) = NULL


        ((@Param1 IS NULL) OR (col1 = @Param1)) AND
        ((@Param2 IS NULL) OR (col2 = @Param2)) AND
        ((@Param3 IS NULL) OR (col3 = @Param3))

    Results in:

    exec TestProc
    exec TestProc I
    exec TestProc I, Love
    exec TestProc I, Love, SPROCs

    LVL 1

    Author Comment

    damn while i was giving points you cam.. I would had split...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now