SQL Server paramtere 
Author Message
 SQL Server paramtere

How do we pass a list of values (array or cursor) to a SQL Server stored
procedure as a parameter


Sat, 20 Dec 2003 06:38:03 GMT  
 SQL Server paramtere
Hello,
you have to declare this parameter as variant. Look at the example:
http://support.microsoft.com/support/kb/articles/Q196/5/90.ASP

regards
Ryszard Gawron


Quote:
> How do we pass a list of values (array or cursor) to a SQL Server stored
> procedure as a parameter



Sat, 20 Dec 2003 17:08:45 GMT  
 SQL Server paramtere
It is not possible to pass an array to a stored procedure since there is
not such a parameter type in stored procedure.

BTW, we do can pass an array to a stored procedure corresponding to each
parameter. For example, if we have a stored procedure with 4 parameters, we
can pass an array with 4 elements to this procedure with each element
corresponding to one parameter.



Sat, 20 Dec 2003 20:46:24 GMT  
 SQL Server paramtere


Fri, 19 Jun 1992 00:00:00 GMT  
 SQL Server paramtere
The example I have written uses a COM object to do most of
the work. This can be placed on the front end if desired.

Form code:

Private Sub cmdInsert_Click()

    Dim lngReturn As Long
    Dim vArgs(7) As Variant
    'Instanciate Class Module
    Dim oCOM As Project1.Class1

        vArgs(0) = txtID.Text
        vArgs(1) = txtFName.Text
        vArgs(2) = txtLName.Text
        vArgs(3) = txtAddress.Text
        vArgs(4) = txtCity.Text
        vArgs(5) = txtState.Text
        vArgs(6) = txtZip.Text

    'Pass parameters using a Long or Boolean to check for
errors
    lngReturn = oCOM.InsertCust(vArgs)

    If lngReturn = 1 Then
        MsgBox "Insert Successful"
    Else
        MsgBox "Failed"
    End If

End Sub

Class Module Code:

Option Explicit
Private oCN As ADODB.Connection
Private oCMD As ADODB.Command
Private oPRM As ADODB.Parameter

Public Function InsertCust(vArgs() As Variant) As Long

On Error GoTo InsertCustErr

    Set oCN = New ADODB.Connection
        oCN.ConnectionString = "File Name=" & App.Path
& "\MSN.udl"
        oCN.CursorLocation = adUseClient
        oCN.Open

    Set oCMD = New ADODB.Command
    Set oCMD.ActiveConnection = oCN
        oCMD.CommandType = adCmdStoredProc
        oCMD.CommandText = "usp_InsertCustomer"

    Set oPRM = New ADODB.Parameter
    Set oPRM = oCMD.CreateParameter("ID", adInteger,
adParamInput, , vArgs(0))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("FName", adVarChar,
adParamInput, 15, vArgs(1))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("LName", adVarChar,
adParamInput, 20, vArgs(2))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("Address", adVarChar,
adParamInput, 50, vArgs(3))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("City", adVarChar,
adParamInput, 20, vArgs(4))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("State", adVarChar,
adParamInput, 2, vArgs(5))
        oCMD.Parameters.Append oPRM
    Set oPRM = oCMD.CreateParameter("Zip", adVarChar,
adParamInput, 10, vArgs(6))
        oCMD.Parameters.Append oPRM

        oCMD.Execute

InsertCustExit:
    Exit Function

InsertCustErr:
    Debug.Print Now & Err.Description
    App.StartLogging App.Path & "\CError.log", vbLogToFile
    App.LogEvent Now & Err.Description, vbLogEventTypeError
    Resume InsertCustExit

End Function

Quote:
>-----Original Message-----
>How do we pass a list of values (array or cursor) to a
SQL Server stored
>procedure as a parameter

>.



Sun, 21 Dec 2003 00:01:17 GMT  
 SQL Server paramtere
What you are doing is accepting the an array in your COM object and
splitting it into arguments to be passed to stored procedures.What I really
want to do is pass a list of values. The number of values may be from 0 -
100. I don't want to be creating the stored procedure with 100 parameters
and giving them different names

But you approach has given me an idea. Why not create the com objects as
DLL. This DLL should have a function which accepts an array(a collection) as
a parameter. We should then declare the function as SQL Server extended
stored procedure.
The question now is, can we call an extended stored procedure from ADO. Also
can we pass a VB array as a parameter?


Quote:
> The example I have written uses a COM object to do most of
> the work. This can be placed on the front end if desired.

> Form code:

> Private Sub cmdInsert_Click()

>     Dim lngReturn As Long
>     Dim vArgs(7) As Variant
>     'Instanciate Class Module
>     Dim oCOM As Project1.Class1

>         vArgs(0) = txtID.Text
>         vArgs(1) = txtFName.Text
>         vArgs(2) = txtLName.Text
>         vArgs(3) = txtAddress.Text
>         vArgs(4) = txtCity.Text
>         vArgs(5) = txtState.Text
>         vArgs(6) = txtZip.Text

>     'Pass parameters using a Long or Boolean to check for
> errors
>     lngReturn = oCOM.InsertCust(vArgs)

>     If lngReturn = 1 Then
>         MsgBox "Insert Successful"
>     Else
>         MsgBox "Failed"
>     End If

> End Sub

> Class Module Code:

> Option Explicit
> Private oCN As ADODB.Connection
> Private oCMD As ADODB.Command
> Private oPRM As ADODB.Parameter

> Public Function InsertCust(vArgs() As Variant) As Long

> On Error GoTo InsertCustErr

>     Set oCN = New ADODB.Connection
>         oCN.ConnectionString = "File Name=" & App.Path
> & "\MSN.udl"
>         oCN.CursorLocation = adUseClient
>         oCN.Open

>     Set oCMD = New ADODB.Command
>     Set oCMD.ActiveConnection = oCN
>         oCMD.CommandType = adCmdStoredProc
>         oCMD.CommandText = "usp_InsertCustomer"

>     Set oPRM = New ADODB.Parameter
>     Set oPRM = oCMD.CreateParameter("ID", adInteger,
> adParamInput, , vArgs(0))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("FName", adVarChar,
> adParamInput, 15, vArgs(1))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("LName", adVarChar,
> adParamInput, 20, vArgs(2))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("Address", adVarChar,
> adParamInput, 50, vArgs(3))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("City", adVarChar,
> adParamInput, 20, vArgs(4))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("State", adVarChar,
> adParamInput, 2, vArgs(5))
>         oCMD.Parameters.Append oPRM
>     Set oPRM = oCMD.CreateParameter("Zip", adVarChar,
> adParamInput, 10, vArgs(6))
>         oCMD.Parameters.Append oPRM

>         oCMD.Execute

> InsertCustExit:
>     Exit Function

> InsertCustErr:
>     Debug.Print Now & Err.Description
>     App.StartLogging App.Path & "\CError.log", vbLogToFile
>     App.LogEvent Now & Err.Description, vbLogEventTypeError
>     Resume InsertCustExit

> End Function

> >-----Original Message-----
> >How do we pass a list of values (array or cursor) to a
> SQL Server stored
> >procedure as a parameter

> >.



Sun, 21 Dec 2003 05:05:22 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. sql server connect. Migration Access->sql server

2. Can not find SQL Server (I'm not using SQL Server)

3. sql server connect. Migration Access->sql server

4. sql server connect. Migration Access->sql server

5. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

6. VB6 application using SQL Server 2000 migrating db to SQL Server 2

7. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

8. Listing Servers Running SQL Server

9. How to rename a SQL Server table in Server Explorer

10. SQL-Optimizer/DBA, the Intelligent Server Monitor for Oracle Servers

11. List SQL Server Servers, Databases and Tables

12. can't install sql server debugging on server

 

 
Powered by phpBB® Forum Software