locked
how to pass a table parameter to my stored procedure RRS feed

  • Question

  • hi all,

    I have a two dimensional info waiting to send to a stored procedure. I want to put them into a table parameter. How can I do  it?

    thanks,

    Shu Zi

    Monday, August 27, 2018 7:26 PM

Answers

  • thanks Alex! Sorry for the late reply for I was out of office for days.
    • Marked as answer by sesquipedalian Thursday, September 6, 2018 2:40 PM
    Thursday, September 6, 2018 2:40 PM
  • thanks Viorel_. sorry for the late reply.
    • Marked as answer by sesquipedalian Thursday, September 6, 2018 2:42 PM
    Thursday, September 6, 2018 2:42 PM

All replies

  • In SQL: use a type defined with CREATE TYPEIn Visual Basic or C#: use DataTable. See: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters.



    • Edited by Viorel_MVP Monday, August 27, 2018 7:56 PM
    • Proposed as answer by Alex-KSGZ Tuesday, August 28, 2018 5:19 AM
    Monday, August 27, 2018 7:55 PM
  • Hi,

    Public Shared Function fnInsertSingleUser(ByVal v_dt As DataTable, ParamArray param As String()) As DataSet
        Try
            Dim cn As SqlConnection = New SqlConnection(connectionString)
            Dim cmd As SqlCommand = cn.CreateCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "" & param(0)
            Dim p As SqlParameter = cmd.Parameters.AddWithValue("@User", v_dt)
            Dim pCode As SqlParameter = cmd.Parameters.AddWithValue("@pCode", param(1))
            Dim gsdm As SqlParameter = cmd.Parameters.AddWithValue("@gsdm", param(2))
            Dim khdm As SqlParameter = cmd.Parameters.AddWithValue("@khdm", param(3))
            Dim qy As SqlParameter = cmd.Parameters.AddWithValue("@qy", param(4))
            Dim ds As DataSet = New DataSet()
            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    SQL

    IF EXISTS ( SELECT  A = 1
                FROM    sys.objects
                WHERE   name = 'PRCreate '
                        AND type = 'P' ) 
        DROP PROCEDURE dbo.PRCreate
     
    go
     
    IF EXISTS ( SELECT  A = 1
                FROM    sys.table_types
                WHERE   name = 'tempPR '
                        AND is_user_defined = 1 ) 
        DROP TYPE dbo.tempPR
    
    
    go
    
    CREATE TYPE dbo.tempPR AS TABLE
    (
    PT_PCURR  decimal(15, 2), 
    MATNR   varchar(18)--
    )
    
    
    go
    
    
    CREATE PROCEDURE dbo.PRCreate
        (
          @User AS dbo.tempPR READONLY, --C# datatable参数
          @pCode varchar(50),
          @gsdm varchar(50),
          @khdm varchar(50),
          @qy varchar(50)
        )
    AS 
        BEGIN
      SELECT * FROM @User 
      END

    call

    Dim ds As DataSet = New DataSet()
     ds = fnInsertSingleUser(dt, "PRCreate", param(0), param(2), param(3), param(4))

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 28, 2018 5:26 AM
  • thanks Alex! Sorry for the late reply for I was out of office for days.
    • Marked as answer by sesquipedalian Thursday, September 6, 2018 2:40 PM
    Thursday, September 6, 2018 2:40 PM
  • thanks Viorel_. sorry for the late reply.
    • Marked as answer by sesquipedalian Thursday, September 6, 2018 2:42 PM
    Thursday, September 6, 2018 2:42 PM