none
Problem Sending Two Table Types to SQL Server Stored Procedure RRS feed

  • Question

  • Can you send two user defined table types to a stored procedure?  I am getting an error when I try to load dt.  The error states "Operand type clash: dbo.udtt_SampleIDs is incompatible with dbo.udtt_StudyRefID"

    Am I doing something wrong?  Because I don't see anything wrong with my code.  Thanks in advance!

    Here is my ADO.NET code:

                Dim dtSampleIDs As DataTable = dtResults.DefaultView.ToTable(True, "vchSampleID")
    
                ' get all unique StudyIDs from 
                Dim dtStudies As DataTable = CType(Me.cv_frmIR.dgvStudies.DataSource, DataView).ToTable(True, "vchStudyRefID")
    
                Using cnn As New SqlClient.SqlConnection(clsDBConn.clsDBConnections.prpConnString)
                    cnn.Open()
    
                    Using cmd As New SqlClient.SqlCommand
    
                        cmd.Connection = cnn
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.CommandText = "[dbo].[ssp_GetSampleIDDataForImport]"
    
                        Dim prm1 As SqlParameter = New SqlParameter("@tblSampleIDs", dtSampleIDs)
                        With prm1
                            .SqlDbType = SqlDbType.Structured
                            .TypeName = "dbo.udtt_SampleIDs"
                        End With
                        cmd.Parameters.Add(prm1)
    
                        Dim prm2 As SqlParameter = New SqlParameter("@tblStudies", dtStudies)
                        With prm1
                            .SqlDbType = SqlDbType.Structured
                            .TypeName = "dbo.udtt_StudyRefID"
                        End With
                        cmd.Parameters.Add(prm2)
    
                        cmd.Parameters.AddWithValue("intMethod_PK", 103)
    
                        dt.Load(cmd.ExecuteReader)
    
                    End Using
                End Using
    

    Here is my stored procedure code:

    ALTER PROC [dbo].[ssp_GetSampleIDDataForImport]
    (
    	@intMethod_PK int,
    	@tblSampleIDs as dbo.udtt_SampleIDs READONLY,
    	@tblStudies as dbo.udtt_StudyRefID READONLY
    )
    AS
    
    SET NOCOUNT ON;
    
    -- create table
    CREATE TABLE #tmpTable(vchSampleID varchar(50), 
    					   vchStudyRefID varchar(25), 
    					   vchSampleType varchar (25),
    					   intAnalysis_PK bigint,
    					   booRunJudge bigint)
    
    INSERT INTO #tmpTable (vchSampleID)
    select vchSampleID
    from @tblSampleIDs
    					   
    CREATE INDEX IDX_SampleID ON #tmpTable(vchSampleID)
    
    -- identify paitents
    UPDATE T
    SET T.vchSampleType = 'Patient', 
    	T.vchStudyRefID = L.vchStudyRefID,
    	T.intAnalysis_PK = RA.intAnalysis_PK
    FROM #tmpTable as T
    	inner join data_Login AS L
    		ON T.vchSampleID = L.vchSampleID
    	inner join @tblStudies as S
    		on L.vchStudyRefID = S.vchStudyRefID
    	INNER JOIN tbl_reg_Analysis as RA
    		ON L.LoginID = RA.intSampleRegID and RA.intSampleClass = 1;
    
    
    -- identify QCs
    UPDATE T
    SET T.vchSampleType = 'QC', 
    	T.intAnalysis_PK = RA.intAnalysis_PK, 
    	T.booRunJudge = isnull(Q.booRunJudge, 0)
    FROM #tmpTable as T
    	inner join tbl_reg_QCPools AS Q
    		ON T.vchSampleID = Q.vchQCID
    	INNER JOIN tbl_reg_Analysis as RA
    		ON Q.intQCPool_PK = RA.intSampleRegID and RA.intSampleClass = 2
    where T.vchSampleType is null;
    	
    
    -- identify calibrators
    UPDATE T
    SET T.vchSampleType = 'Calibrator',
    	T.intAnalysis_PK = RA.intAnalysis_PK
    FROM #tmpTable as T
    	inner join tbl_reg_Calibrators AS C
    		ON T.vchSampleID = C.vchCalibratorName
    	INNER JOIN tbl_reg_Analysis as RA
    		ON C.bintCalibrator_ID = RA.intSampleRegID and RA.intSampleClass = 3
    where C.intMethod_PK = @intMethod_PK
    	and T.vchSampleType is null;
    	
    select *
    from #tmpTable;
    


    Ryan
    Tuesday, September 27, 2011 8:37 PM

Answers

  • I just noticed when I'm setting up my second parameter I am using prm1 in my With statement.  I just changed it to this:

                        Dim prm2 As SqlParameter = New SqlParameter("@tblStudies", dtStudies)
                        With prm2
                            .SqlDbType = SqlDbType.Structured
                            .TypeName = "dbo.udtt_StudyRefID"
                        End With
    

    Thanks

     


    Ryan
    • Marked as answer by Ryan0827 Wednesday, September 28, 2011 1:19 PM
    Wednesday, September 28, 2011 1:19 PM

All replies

  • Hi Ryan,

    Welcome!

    Please try to change this line: 

    >>cmd.Parameters.AddWithValue("intMethod_PK", 103) to 

    cmd.Parameters.AddWithValue("@intMethod_PK", 103)// add @

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 8:56 AM
    Moderator
  • I just noticed when I'm setting up my second parameter I am using prm1 in my With statement.  I just changed it to this:

                        Dim prm2 As SqlParameter = New SqlParameter("@tblStudies", dtStudies)
                        With prm2
                            .SqlDbType = SqlDbType.Structured
                            .TypeName = "dbo.udtt_StudyRefID"
                        End With
    

    Thanks

     


    Ryan
    • Marked as answer by Ryan0827 Wednesday, September 28, 2011 1:19 PM
    Wednesday, September 28, 2011 1:19 PM