VB 创建SQL存储过程并执行调用

@​​TOC​​ 业务流程：从VB程序中获取到数据库存储参数，在数据库中查询该参数对应ID字段，将对应的参数转换完成后进行存储到数据库对应的数据展示集合表中。

创建SQL存储过程

有参传递，通过游标将存进来的数值进行获取。

CREATE PROCEDURE [dbo].[pr_FPICSSPCElectrical]
    @sgrp as int, -- 子组以及各个时间
    @part as varchar(255), -- 产品名
    @prcs as varchar(255), -- 过程名
    @work as varchar(255), -- 工作站
    @lot as varchar(255), -- 批次
    @emp as varchar(255), -- 员工名称
    @sample as int, -- 样本量
    @testname as varchar(255), -- 测试名
    @testno as int, -- 测试编号
    @sbtestno as int, -- 子测试
    @resultval as float -- 相乘的取值
    -- @Result AS int OUTPUT--返回值，若为1则满足，为0则不满足
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @F_PART AS INT, @F_PRCS AS INT, @F_WKST AS INT, @F_LOT AS INT, @F_EMPL AS INT, @F_TEST AS INT

    -- set @Result=1
    --产品名转换
    IF EXISTS(SELECT 1 FROM PART_DAT WHERE F_NAME = @part)
        BEGIN
            -- 声明游标
            DECLARE PART_VALUE CURSOR FOR
                SELECT F_PART FROM PART_DAT WHERE F_NAME = @part
            -- 打开游标
            OPEN PART_VALUE
            -- 取第一条记录
            FETCH NEXT FROM PART_VALUE INTO @F_PART
            CLOSE PART_VALUE
            -- 释放游标
            DEALLOCATE PART_VALUE
        END
    --过程名转换
    IF EXISTS(SELECT 1 FROM PRCS_DAT WHERE F_NAME = @prcs)
        BEGIN
            -- 声明游标
            DECLARE PRCS_VALUE CURSOR FOR
                SELECT F_PRCS FROM PRCS_DAT WHERE F_NAME = @prcs
            -- 打开游标
            OPEN PRCS_VALUE
            -- 取第一条记录
            FETCH NEXT FROM PRCS_VALUE INTO @F_PRCS
            CLOSE PRCS_VALUE
            -- 释放游标
            DEALLOCATE PRCS_VALUE
        END
    --工作站转换
    IF EXISTS(SELECT 1 FROM WKST_INF WHERE F_NAME = @work)
        BEGIN
            DECLARE WKST_VALUE CURSOR FOR
                SELECT F_WKST FROM WKST_INF WHERE F_NAME = @work
            OPEN WKST_VALUE
            FETCH NEXT FROM WKST_VALUE INTO @F_WKST
            CLOSE WKST_VALUE
            -- 释放游标
            DEALLOCATE WKST_VALUE
        END
    -- 批次转换
    IF EXISTS(SELECT 1 FROM PART_LOT WHERE F_NAME = @lot)
        BEGIN
            DECLARE LOT_VALUE CURSOR FOR
                SELECT F_LOT FROM PART_LOT WHERE F_NAME = @lot
            OPEN LOT_VALUE
            FETCH NEXT FROM LOT_VALUE INTO @F_LOT
            CLOSE LOT_VALUE
            -- 释放游标
            DEALLOCATE LOT_VALUE
        END
    -- 员工转换
    IF EXISTS(SELECT 1 FROM EMPL_INF WHERE F_NAME = @emp)
        BEGIN
            DECLARE EMPL_VALUE CURSOR FOR
                SELECT F_EMPL FROM EMPL_INF WHERE F_NAME = @emp
            OPEN EMPL_VALUE
            FETCH NEXT FROM EMPL_VALUE INTO @F_EMPL
            CLOSE EMPL_VALUE
            -- 释放游标
            DEALLOCATE EMPL_VALUE
        END
    -- 测试名转换
    IF EXISTS(SELECT 1 FROM TEST_DAT WHERE F_NAME = @testname)
        BEGIN
            DECLARE TESTNAME_VALUE CURSOR FOR
                SELECT F_TEST FROM TEST_DAT WHERE F_NAME = @testname
            OPEN TESTNAME_VALUE
            FETCH NEXT FROM TESTNAME_VALUE INTO @F_TEST
            CLOSE TESTNAME_VALUE
            -- 释放游标
            DEALLOCATE TESTNAME_VALUE
        END
    BEGIN
        INSERT INTO SGRP_INF(F_SGRP, F_CRTM, F_PART, F_EDTM, F_PRCS, F_WKNO, F_LOT, F_EMPL, F_SGTM, F_SGSZ, F_USER, F_TRTM)
        VALUES(@sgrp,@sgrp,@F_PART,@sgrp,@F_PRCS,@F_WKST,@F_LOT,@F_EMPL,@sgrp,@sample,@F_EMPL,@sgrp)
        INSERT INTO SGRP_TST(F_SGRP, F_TEST, F_TSNO, F_SBNO, F_EDTM, F_VAL, F_USER, F_TRTM)
        VALUES(@sgrp,@F_TEST,@testno,@sbtestno,@sgrp,@resultval,@F_EMPL,@sgrp)
    END
END
go

使用存储过程测试

declare @Result int
exec pr_FPICSSPCElectrical 1667886283 ,'测试数据','测试数据',
  '测试数据','测试数据','测试数据',0,'测试数据',1,
  '0',1.0,@Result output

VB中执行调用SQL存储过程

'获取数据源参数
Dim prname As String = productname.Text     
Dim p1 As String = Left(prname, 8)
Dim p2 As String = Mid(prname, 12, 2)
Dim part As String = p1 & p2 & "_ICS"          
Dim specname As String = specnametext.Text  
Dim prcs As String
If InStr(specname, "飞针") > 0 Then
    prcs = "ICS_飞针电测"
ElseIf InStr(specname, "治具") > 0 Then
    prcs = "ICS_治具电测"
Else
    prcs = "ICS_电测"
End If
Dim work As String = Environ("computername")
Dim lotno As String = SelectionIdField.TextControl.Text
Dim emp As String = Session("UserName").ToString
Dim uresult As Integer = Me.fpUnitPerStripField.TextControl.Text
Dim qty3 As Integer = Me.fpMaxRejectQty3Field.TextControl.Text
Dim sample As Integer = Convert.ToInt32(uresult * qty3)
Dim testno As Integer = 1
Dim sbtestno As Integer = 0
Dim resultval As Single
Dim sgrp As Integer = DateDiff("s", "1970-01-01 00:00:00", Now)   
Dim sgrp1 As Integer
If InStr(specname, "电测") > 0 Then 
    Dim testname As String
    sgrp1 = sgrp
    Dim intRowsAff As Integer
    For Each oRow As Infragistics.WebUI.UltraWebGrid.UltraGridRow In DetailsField.Rows
        '数据库操作从这开始
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim cnStr As String = "Data Source=数据库IP地址;Integrated Security=False;uid=用户名;pwd=密码;Database=数据库名;"
        cn = New SqlConnection(cnStr)
        cn.Open()
        '执行存储过程
        cmd = New SqlCommand("pr_FPICSSPCElectrical", cn)
        cmd.CommandType = CommandType.StoredProcedure
        resultval = oRow.Cells.FromKey("RejectQty").Value
        testname = oRow.Cells.FromKey("LossReason").Value
        If InStr(testname, "开路") > 0 Then
            testname = "开路缺陷数量_电测_ICS"
        ElseIf InStr(testname, "短路") > 0 Then
            testname = "短路缺陷数量_电测_ICS"
        Else
            Continue For
        End If
        cmd.Parameters.Add("@sgrp", SqlDbType.Int)
        cmd.Parameters("@sgrp").Value = sgrp1
        cmd.Parameters.Add("@part", SqlDbType.VarChar, 255)
        cmd.Parameters("@part").Value = part
        cmd.Parameters.Add("@prcs", SqlDbType.VarChar, 255)
        cmd.Parameters("@prcs").Value = prcs
        cmd.Parameters.Add("@work", SqlDbType.VarChar, 255)
        cmd.Parameters("@work").Value = work
        cmd.Parameters.Add("@lot", SqlDbType.VarChar, 255)
        cmd.Parameters("@lot").Value = lotno
        cmd.Parameters.Add("@emp", SqlDbType.VarChar, 255)
        cmd.Parameters("@emp").Value = emp
        cmd.Parameters.Add("@sample", SqlDbType.Int)
        cmd.Parameters("@sample").Value = sample
        cmd.Parameters.Add("@testname", SqlDbType.VarChar, 255)
        cmd.Parameters("@testname").Value = testname
        cmd.Parameters.Add("@testno", SqlDbType.Int)
        cmd.Parameters("@testno").Value = testno
        cmd.Parameters.Add("@sbtestno", SqlDbType.Int)
        cmd.Parameters("@sbtestno").Value = sbtestno
        cmd.Parameters.Add("@resultval", SqlDbType.Float)
        cmd.Parameters("@resultval").Value = resultval
        Try
            intRowsAff = cmd.ExecuteNonQuery()
            cmd.Dispose()
        Catch ex As Exception
            intRowsAff = 0
            Me.DisplayMessage(ex.Message & ex.Source, False)
        End Try
        sgrp1 += 1
    Next
    If intRowsAff = 0 Then
        DisplayMessage("数据采集失败", False)
    Else
        DisplayMessage("数据采集成功", False)
    End If
End If