Retrieving the stored procedure parameter list in ado.net

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Irishmaninusa (jdaly_at_structuctureinteractive.com.takemeoffifyouwantoemailme)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 16:36:43 -0400

http://www.15seconds.com/issue/010718.htm

I am current writing a data access layer for a project I am on, and one of
the things I want to be able to do is use stored procedures with optional
values. The stored procedure part is not an issue, what is the issue is how
I call the stored procedure using ado.net. Currently I get the list of
parameters for the sp and store them in an array.

The current way that I add them to the command object is as follow(s):

'Traverse through the array of parameters for the stored procedure

For iLoop = 0 To iArrPost - 1

.Parameters.Add(arrParamList(iLoop).sParamName, _

arrParamList(iLoop).enuDataType, _

arrParamList(iLoop).iSize).Value = arrParamList(iLoop).oValue

.Parameters(iLoop).Direction = arrParamList(iLoop).enuDirection

Next

The problem with this is if the stored proceudre is like this

param1

param2

param 3

param4

with param1 being required and the rest optional, then if pass in param1 and
param3, it is going to write out the contents of param3 to be sent to param2
and that is what I am trying to avoid. So the code I found about is from
ado, but I am not sure if this is possible to do in ado.net or if there is a
clearer and better way of doing it. So if any one has any insight into this
please let me know. Thanks

JD

Relevant Pages

  • Re: sql expression
    ... Create a stored procedure ... similar to that shown below in SQL Enterprise Manager. ... param2 and param3) or whatever and how many ...
    (microsoft.public.vb.crystal)
  • Re: Retrieving the stored procedure parameter list in ado.net
    ... The stored procedure part is not an issue, ... > parameters for the sp and store them in an array. ... > with param1 being required and the rest optional, ... > param3, it is going to write out the contents of param3 to be sent to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: sql expression
    ... How can I pass parameters to stored procedure in Crystal 9? ... my_stored_proc({param1}, {param2}, ). ... I want to pass it externally (parameter field of formula field). ...
    (microsoft.public.vb.crystal)
  • Error with BigDecimal used as stored procedure parameter
    ... if (@param1 is NULL) ... Input Parameter Scale = 3 ... Note that I use BigDecimal as the parameter type (which is the recommended ... Given the stored procedure, I would have expected the value 300.11 as the ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Using Stored Procedure Variables
    ... I have a variable I want to use in a Stored Procedure. ... The stored procedure then uses that in the where clause like ... 'Select * from Sometable where @param1' When I try and do this it fails. ... there a way to concatonate the variable into a where clause? ...
    (microsoft.public.sqlserver.programming)