C#调用存储过程的 output 参数：string[20]:size 属性具有无效大小值0 - 不及格的程序员-八神

公告

C#调用存储过程的 output 参数：string[20]:size 属性具有无效大小值0

string[12] 是参数的索引号基于0的，所以这里是第13个参数有问题

    --开关参数
    declare @bFilter_cBillCode bit = 0 --是否过滤单号
    declare @bFilter_cWareCode bit = 0--是否过滤商品编码
    
    declare @bUpdate_nPrice bit  = 0--是否更新价格
    declare @bUpdate_SyncPrice_XSHT bit = 0 --是否更新销售合同价格
    declare @bUpdate_SyncPrice_XSTH bit =0 --是否更新销退单价格
    declare @bUpdate_nTaxRate bit = 0 --是否更新税率
    declare @bUpdate_cClientCode bit = 0 --是否更新购货者
    
    --值参数
    declare @cBillCode varchar(32) = ''--单号
    declare @cWareCode varchar(32) = ''--商品编码
    declare @nPrice numeric(18, 2)= 0 --价格
    declare @nTaxRate numeric(6, 2) =0  --税率
    declare @cClientCode varchar(32) = '' --购货者编码
    
    declare @cErrMessage varchar(256) = 'uu' --返回参数，错误提示 这里一定要写长度 不然会提示出错：string[12]:size 属性具有无效大小值0
    exec pJxcTool_Sale @bFilter_cBillCode , @bFilter_cWareCode ,@bUpdate_nPrice , @bUpdate_SyncPrice_XSHT, @bUpdate_SyncPrice_XSTH,@bUpdate_nTaxRate, @bUpdate_cClientCode, @cBillCode, @cWareCode , @nPrice, @nTaxRate , @cClientCode, @cErrMessage output
    select @cErrMessage

在查询分析器中不需要声明尺寸大小，但C#里调用就要声明

                    System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand(
                        string.Format("dycw{0}..pJxcTool_Purchase", accID), conn);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    System.Data.SqlClient.SqlParameter sqlParam = null;
                    。。。
                    sqlParam = new System.Data.SqlClient.SqlParameter("@cErrMessage", SqlDbType.VarChar, 256);
                    sqlParam.Direction = ParameterDirection.Output;
                    sqlcmd.Parameters.Add(sqlParam);
                    var iResult = sqlcmd.ExecuteNonQuery();
                    return string.Format("{0}", sqlParam.Value);

USE [dycw_FuXinAnTu]
GO
/****** Object:  StoredProcedure [dbo].[pJxcTool_Sale]    Script Date: 01/10/2024 14:19:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--2023-01-30 调整工具，销售
ALTER proc [dbo].[pJxcTool_Sale]
    --开关参数
    @bFilter_cBillCode bit, --是否过滤单号
    @bFilter_cWareCode bit, --是否过滤商品编码
    
    @bUpdate_nPrice bit, --是否更新价格
    @bUpdate_SyncPrice_XSHT bit, --是否更新销售合同价格
    @bUpdate_SyncPrice_XSTH bit, --是否更新销退单价格
    @bUpdate_nTaxRate bit, --是否更新税率
    @bUpdate_cClientCode bit, --是否更新购货者
    
    --值参数
    @cBillCode varchar(32), --单号
    @cWareCode varchar(32), --商品编码
    @nPrice numeric(18, 2), --价格
    @nTaxRate numeric(6, 2), --税率
    @cClientCode varchar(32), --购货者编码
    
    @cErrMessage varchar(256) output --返回参数，错误提示
    
as

set @cErrMessage = ''

if @bFilter_cBillCode = 1 and @cBillCode = ''
begin
    set @cErrMessage = '单号不能为空！'
    return;
end

if @bFilter_cBillCode = 0 and @bFilter_cWareCode = 1
begin
    set @cErrMessage = '过虑产品编码时，销售单号也需要一并过滤！'
    return;
end

if @bUpdate_nPrice = 1 and (@bFilter_cBillCode = 0 or @bFilter_cWareCode = 0)
begin
    set @cErrMessage = '更新销售价格时，销售单号和产品编码需要一并过滤！'
    return;
end

if @bFilter_cBillCode = 0 and @bUpdate_cClientCode = 1
begin
    set @cErrMessage = '更新购货者时，销售单号需要过滤！'
    return;
end

if exists (select 1 from tSaleForm where cBillCode = @cBillCode)
begin
    select @cBillCode = cInvoiceCode from tSaleForm where cBillCode = @cBillCode
end

if @bFilter_cBillCode = 1 and not exists (select 1 from tSaleForm where cInvoiceCode = @cBillCode)
begin
    set @cErrMessage = '单号不存在！'
    return;
end

if @bFilter_cWareCode = 1 and not exists (select 1 from tWare where cWareCode = @cWareCode and iYear = (select cKeyValue from tBasicParamentSub where iKeyID = 13))
begin
    set @cErrMessage = '产品编码不存在！'
    return;
end

declare @cSQL varchar(4000)
set @cSQL = ''

begin try

    begin tran --开始事务
    
    --供货者
    if @bUpdate_cClientCode = 1
    begin
        set @cSQL = ''
        set @cSQL = @cSQL + 'update tSaleForm set cCode_Client = ''' + @cClientCode + ''''
        set @cSQL = @cSQL + ' where cInvoiceCode = ''' + @cBillCode + ''' '
        exec (@cSQL)
    end

    --税率
    if @bUpdate_nTaxRate = 1
    begin
        set @cSQL = ''
        set @cSQL = @cSQL + 'update tSaleFormSub set nTaxRate = ' + cast(@nTaxRate as varchar(20))
        set @cSQL = @cSQL + ' where cInvoiceCode = ''' + @cBillCode + ''' '
        exec (@cSQL)
        
        set @cSQL = ''
        set @cSQL = @cSQL + 'update sub set '
        set @cSQL = @cSQL + ' sub.mSum = round(round(sub.nPriceTax * sub.nQntty, 2) / (1 + sub.nTaxRate / 100.0), 2) '
        set @cSQL = @cSQL + ' , sub.mTaxSum = round(sub.nPriceTax * sub.nQntty, 2) - round(round(sub.nPriceTax * sub.nQntty, 2) / (1 + sub.nTaxRate / 100.0), 2) '
        set @cSQL = @cSQL + ' , sub.nButTaxPrice = round(sub.nPriceTax / (1 + sub.nTaxRate / 100.0), 4) '
        set @cSQL = @cSQL + ' from tSaleFormSub sub inner join tSaleForm main on sub.cInvoiceCode = main.cInvoiceCode '
        set @cSQL = @cSQL + ' where main.cInvoiceCode = ''' + @cBillCode + ''' and sub.cWareCode = ''' + @cWareCode + ''''
        print @cSQL
        exec (@cSQL)            
    end

    --单价
    if @bUpdate_nPrice = 1 and @cBillCode != '' and @cWareCode != ''
    begin
        set @cSQL = ''
        set @cSQL = @cSQL + 'update tSaleFormSub set nPriceTax = ' + cast(@nPrice as varchar(20))
        set @cSQL = @cSQL + ' where cInvoiceCode = ''' + @cBillCode + ''' and cWareCode = ''' + @cWareCode + ''''
        print @cSQL
        exec (@cSQL)
        
        set @cSQL = ''
        set @cSQL = @cSQL + 'update sub set sub.mTotalSum = round(sub.nPriceTax * sub.nQntty, 2)'
        set @cSQL = @cSQL + ' , sub.mSum = round(round(sub.nPriceTax * sub.nQntty, 2) / (1 + sub.nTaxRate / 100.0), 2) '
        set @cSQL = @cSQL + ' , sub.mTaxSum = round(sub.nPriceTax * sub.nQntty, 2) - round(round(sub.nPriceTax * sub.nQntty, 2) / (1 + sub.nTaxRate / 100.0), 2) '
        set @cSQL = @cSQL + ' , sub.nButTaxPrice = round(sub.nPriceTax / (1 + sub.nTaxRate / 100.0), 4) '
        set @cSQL = @cSQL + ' from tSaleFormSub sub inner join tSaleForm main on sub.cInvoiceCode = main.cInvoiceCode '
        set @cSQL = @cSQL + ' where main.cInvoiceCode = ''' + @cBillCode + ''' and sub.cWareCode = ''' + @cWareCode + ''''
        print @cSQL
        exec (@cSQL)    
        
        --sp_help tSaleOrderFormSub
        
        set @cSQL = ''
        set @cSQL = @cSQL + 'update main set main.mDueSum = sub.mTotalSum from tSaleForm main inner join (select cInvoiceCode, sum(mTotalSum) as mTotalSum from tSaleFormSub group by cInvoiceCode) sub on main.cInvoiceCode = sub.cInvoiceCode'
        set @cSQL = @cSQL + ' where main.cInvoiceCode = ''' + @cBillCode + ''' '
        print @cSQL
        exec (@cSQL)
        
        if @bUpdate_SyncPrice_XSHT = 1
        begin
            set @cSQL = ''
            set @cSQL = @cSQL + 'update tSaleOrderFormSub set nPriceButTax = ' + cast(@nPrice as varchar(20))
            set @cSQL = @cSQL + ' where cCode_SaleOrdFrm in (select cCode_SaleOrdFrm from tSaleForm where cInvoiceCode = ''' + @cBillCode + ''') and cWareCode = ''' + @cWareCode + ''''
            print @cSQL
            exec (@cSQL)
            
            set @cSQL = ''
            set @cSQL = @cSQL + 'update sub set sub.mSumButTax = round(nPriceButTax * nOrdQntty, 2)'
            set @cSQL = @cSQL + ' from tSaleOrderFormSub sub inner join tSaleOrderForm main on sub.cCode_SaleOrdFrm = main.cCode_SaleOrdFrm '
            set @cSQL = @cSQL + ' where main.cCode_SaleOrdFrm in (select cCode_SaleOrdFrm from tSaleForm where cInvoiceCode = ''' + @cBillCode + ''') and sub.cWareCode = ''' + @cWareCode + ''''
            print @cSQL
            exec (@cSQL)    
            
            set @cSQL = ''
            set @cSQL = @cSQL + 'update main set main.mTotalSum = sub.mTotalSum from tSaleOrderForm main inner join (select cCode_SaleOrdFrm, sum(mSumButTax) as mTotalSum from tSaleOrderFormSub group by cCode_SaleOrdFrm) sub on main.cCode_SaleOrdFrm = sub.cCode_SaleOrdFrm'
            set @cSQL = @cSQL + ' where main.cCode_SaleOrdFrm in (select cCode_SaleOrdFrm from tSaleForm where cInvoiceCode = ''' + @cBillCode + ''') '
            print @cSQL
            exec (@cSQL)
                
        end
        
        if @bUpdate_SyncPrice_XSTH = 1
        begin
            set @cSQL = ''
            set @cSQL = @cSQL + 'update xtsub set xtsub.nPriceTax = xssub.nPriceTax '
            set @cSQL = @cSQL + ' from tSaleFormSub xtsub inner join tSaleFormSub xssub on xtsub.cRefBillCode = xssub.cInvoiceCode and xtsub.iRefID = xssub.ID '
            set @cSQL = @cSQL + ' where xssub.cInvoiceCode = ''' + @cBillCode + ''' and xssub.cWareCode = ''' + @cWareCode + ''''
            print @cSQL
            exec (@cSQL)
            
            set @cSQL = ''
            set @cSQL = @cSQL + 'update xtsub set xtsub.mTotalSum = round(xssub.nPriceTax * xtsub.nQntty, 2)'
            set @cSQL = @cSQL + ' , xtsub.mSum = round(round(xssub.nPriceTax * xtsub.nQntty, 2) / (1 + xssub.nTaxRate / 100.0), 2) '
            set @cSQL = @cSQL + ' , xtsub.mTaxSum = round(xssub.nPriceTax * xtsub.nQntty, 2) - round(round(xssub.nPriceTax * xtsub.nQntty, 2) / (1 + xssub.nTaxRate / 100.0), 2) '
            set @cSQL = @cSQL + ' , xtsub.nButTaxPrice = round(xssub.nPriceTax / (1 + xssub.nTaxRate / 100.0), 4) '
            set @cSQL = @cSQL + ' from tSaleFormSub xtsub inner join tSaleFormSub xssub on xtsub.cRefBillCode = xssub.cInvoiceCode and xtsub.iRefID = xssub.ID '
            set @cSQL = @cSQL + '  inner join tSaleForm xtmain on xtsub.cInvoiceCode = xtmain.cInvoiceCode '
            set @cSQL = @cSQL + ' where xssub.cInvoiceCode = ''' + @cBillCode + ''' and xssub.cWareCode = ''' + @cWareCode + ''''
            print @cSQL
            exec (@cSQL)
            
            set @cSQL = ''
            set @cSQL = @cSQL + 'update main set main.mDueSum = sub.mTotalSum from tSaleForm main inner join '
            set @cSQL = @cSQL + '(select cInvoiceCode, sum(mTotalSum) as mTotalSum from tSaleFormSub '
            set @cSQL = @cSQL + ' where cRefBillCode in (select cInvoiceCode from tSaleFormSub where cInvoiceCode = ''' + @cBillCode + ''')'
            set @cSQL = @cSQL + ' group by cInvoiceCode) sub on main.cInvoiceCode = sub.cInvoiceCode'
            print @cSQL
            exec (@cSQL)
                    
        end    
        
        
    end

    --sp_help tSaleOrderForm



    if @@trancount > 0
    begin
        commit tran 
    end    
    
end try
begin catch
    select @cErrMessage =  ERROR_MESSAGE()
    
    if @@trancount > 0
    begin
        rollback tran 
    end    
end catch

posted on 2023-01-30 14:30 不及格的程序员-八神阅读(102) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部