[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Change dynamically number of arguments

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 ?
0
PhilippeRenaud
Asked:
PhilippeRenaud
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you work with a list of values for the same stuff?
http://www.experts-exchange.com/A_1536.html

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
as
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
0
 
Pratima PharandeCommented:
you can change the store procedure to accetp optiona lparameter
someting like tghis example

CREATE PROCEDURE TestProc
(
    @Param1 varchar(50) = NULL,
    @Param2 varchar(50) = NULL,
    @Param3 varchar(50) = NULL
)
AS
SELECT
    *

FROM
    TestTable

WHERE
    ((@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

0
 
PhilippeRenaudAuthor Commented:
damn while i was giving points you cam.. I would had split...
sorry!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now