错哪里？

Question

goif exists (select 1
            from  sysobjects
           where  id = object_id('udsp_InsertOperationEnterprise')
            and   type = 'P')
   drop procedure udsp_InsertOperationEnterprisego/*----------------------------------------------------------------------------------------------------*/
/*                                                                                                    */
/*                                                                                                    */
/*                          用户自定义存储过程udsp_InsertOperationEnterprise脚本内容                    */
/*                                                                                                    */
/*                                                                                                    */
/*----------------------------------------------------------------------------------------------------*/create procedure udsp_InsertOperationEnterprise
@Name nvarchar(64),
@ShortName nvarchar(32),
@Address nvarchar(128),
@AreaName nvarchar(16),
@ArtificialPerson nvarchar(16),
@ArtificialPersonTel nvarchar(32),
@ArtificialPersonFax nvarchar(32),
@Constituent nvarchar(16),
@ConstituentTel nvarchar(32),
@ConstituentFax nvarchar(32),
@CableAddress nvarchar(32),
@PostCode nvarchar(32),
@AccountBank nvarchar(128),
@BankAccount nvarchar(32),
@RatePayingAccount nvarchar(32),
@ImportVendorEnterpriseTag bit,
@ImportVendeeEnterpriseTag bit,
@ExportVendorEnterpriseTag bit,
@ExportVendeeEnterpriseTag bit,
@TransportationEnterpriseTag bit,
@ConsigneeEnterpriseTag bit,
@AssayEnterpriseTag bit,
@SpotcheckEnterpriseTag bit,
@TransportationEnterpriseName nvarchar(64),
@ConsigneeEnterpriseName nvarchar(64)
as
        declare @Index smallint
        declare @RecordAmount smallint
set @RecordAmount=(select count(*) from udfdt_Department)
        if (@RecordAmount=0) 
        begin
set @Index=1
    end
else
begin
set @Index=(select max(df_Index) from udfdt_Department)+1
end
declare @AreaIndex smallint
set @AreaIndex=isnull((select oeaf_Index from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName),0)
declare @TransportationEnterpriseIndex smallint
set @TransportationEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@TransportationEnterpriseName),0)
declare @ConsigneeEnterpriseIndex smallint
set @ConsigneeEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@ConsigneeEnterpriseName),0)
        declare @InsertStatement nvarchar(4000)
   set @InsertStatement='insert into udsdt_OperationEnterprise (oef_Index,
     oef_Name,
     oef_ShortName,
     oef_Address,
     oef_AreaIndex,
     oef_ArtificialPerson,
     oef_ArtificialPersonTel,
     oef_ArtificialPersonFax,
     oef_Constituent,
     oef_ConstituentTel,
     oef_ConstituentFax,
     oef_CableAddress,
     oef_PostCode,
     oef_AccountBank,
     oef_BankAccount,
     oef_RatePayingAccount,
     oef_ImportVendorEnterpriseTag,
     oef_ImportVendeeEnterpriseTag,
     oef_ExportVendorEnterpriseTag,
     oef_ExportVendeeEnterpriseTag,
                     oef_TransportationEnterpriseTag,
     oef_ConsigneeEnterpriseTag,
     oef_AssayEnterpriesTag,
     oef_SpotcheckEnterpriseTag,
     oef_TransportationEnterpriseIndex,
     oef_ConsigneeEnterpriseIndex) values ('+ 
     convert(nvarchar(10),@Index)+
     ','''+@Name+''','+
     ','''+@ShortName+''','+
     ','''+@Address+''','+
                     convert(nvarchar(10),@AreaIndex)+','+
     ','''+@ArtificialPerson+''','+
     ','''+@ArtificialPersonTel+''','+
     ','''+@ArtificialPersonFax+''','+
     ','''+@Constituent+''','+
     ','''+@ConstituentTel+''','+
     ','''+@ConstituentFax+''','+
     ','''+@CableAddress+''','+
     ','''+@PostCode+''','+
     ','''+@AccountBank+''','+
     ','''+@BankAccount+''','+
     ','''+@RatePayingAccount+''','+
     convert(nvarchar(10),@ImportVendorEnterpriseTag)+','+
                                                                     convert(nvarchar(10),@ImportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendorEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseTag)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseTag)+','+
     convert(nvarchar(10),@AssayEnterpriseTag)+','+
     convert(nvarchar(10),@SpotcheckEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseIndex)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseIndex)+')'  
if exists(select 1 from udsdt_OperationEnterprise where oef_Name=@Name)
raiserror('已存在同名记录',16,1)
else
execute(@InsertStatement)goexec udsp_insertoperationenterprise '1212','1','','1','','','','','','','','','','','',1,1,1,1,1,1,1,1,'1','1'动态语句尺寸已经放到了最大值4K了，但以执行的时候还是报告如下的错误，已经检查了所有的逗号了问题出在哪里，哪位帮忙看一下！

Answer 1

declare @InsertStatement nvarchar(4000)
   set @InsertStatement='insert into udsdt_OperationEnterprise (oef_Index,
     oef_Name,
     oef_ShortName,
     oef_Address,
     oef_AreaIndex,
     oef_ArtificialPerson,
     oef_ArtificialPersonTel,
     oef_ArtificialPersonFax,
     oef_Constituent,
     oef_ConstituentTel,
     oef_ConstituentFax,
     oef_CableAddress,
     oef_PostCode,
     oef_AccountBank,
     oef_BankAccount,
     oef_RatePayingAccount,
     oef_ImportVendorEnterpriseTag,
     oef_ImportVendeeEnterpriseTag,
     oef_ExportVendorEnterpriseTag,
     oef_ExportVendeeEnterpriseTag,
                     oef_TransportationEnterpriseTag,
     oef_ConsigneeEnterpriseTag,
     oef_AssayEnterpriesTag,
     oef_SpotcheckEnterpriseTag,
     oef_TransportationEnterpriseIndex,
     oef_ConsigneeEnterpriseIndex) values ('+ 
     convert(nvarchar(10),@Index)+
     ','''+@Name+''','+
     ','''+@ShortName+''','+
     ','''+@Address+''','+
                     convert(nvarchar(10),@AreaIndex)+','+
     ','''+@ArtificialPerson+''','+
     ','''+@ArtificialPersonTel+''','+
     ','''+@ArtificialPersonFax+''','+
     ','''+@Constituent+''','+
     ','''+@ConstituentTel+''','+
     ','''+@ConstituentFax+''','+
     ','''+@CableAddress+''','+
     ','''+@PostCode+''','+
     ','''+@AccountBank+''','+
     ','''+@BankAccount+''','+
     ','''+@RatePayingAccount+''','+
     convert(nvarchar(10),@ImportVendorEnterpriseTag)+','+
                                                                     convert(nvarchar(10),@ImportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendorEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseTag)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseTag)+','+
     convert(nvarchar(10),@AssayEnterpriseTag)+','+
     convert(nvarchar(10),@SpotcheckEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseIndex)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseIndex)+')'  
if exists(select 1 from udsdt_OperationEnterprise where oef_Name=@Name)
raiserror('已存在同名记录',16,1)
else
execute(@InsertStatement)

Answer 2

--try
declare @InsertStatement nvarchar(4000) 
-->
declare @InsertStatement varchar(8000)

Answer 3

服务器: 消息 2717，级别 16，状态 2，过程 udsp_InsertOperationEnterprise，行 0
赋予 参数 '@InsertStatement' 的大小（8000）超过了最大允许值（4000）。
参数 '@InsertStatement' 的数据类型无效。动态语句最大就是4K，到不了8K啊！

Answer 4

set @InsertStatement='insert into udsdt_OperationEnterprise (oef_Index, 
    oef_Name, 
    oef_ShortName, 
    oef_Address, 
    oef_AreaIndex, 
    oef_ArtificialPerson, 
    oef_ArtificialPersonTel, 
    oef_ArtificialPersonFax, 
    oef_Constituent, 
    oef_ConstituentTel, 
    oef_ConstituentFax, 
    oef_CableAddress, 
    oef_PostCode, 
    oef_AccountBank, 
    oef_BankAccount, 
    oef_RatePayingAccount, 
    oef_ImportVendorEnterpriseTag, 
    oef_ImportVendeeEnterpriseTag, 
    oef_ExportVendorEnterpriseTag, 
    oef_ExportVendeeEnterpriseTag, 
                    oef_TransportationEnterpriseTag, 
    oef_ConsigneeEnterpriseTag, 
    oef_AssayEnterpriesTag, 
    oef_SpotcheckEnterpriseTag, 
    oef_TransportationEnterpriseIndex, 
    oef_ConsigneeEnterpriseIndex) values ('+ 
    convert(nvarchar(10),@Index)+ 
    ','''+@Name+''''+ 
    ','''+@ShortName+''''+ 
    ','''+@Address+''','+ 
                    convert(nvarchar(10),@AreaIndex)+','+ 
    ','''+@ArtificialPerson+''''+ 
    ','''+@ArtificialPersonTel+''''+ 
    ','''+@ArtificialPersonFax+''''+ 
    ','''+@Constituent+''''+ 
    ','''+@ConstituentTel+''''+ 
    ','''+@ConstituentFax+''''+ 
    ','''+@CableAddress+''''+ 
    ','''+@PostCode+''''+ 
    ','''+@AccountBank+''''+ 
    ','''+@BankAccount+''''+ 
    ','''+@RatePayingAccount+''','+ 
    convert(nvarchar(10),@ImportVendorEnterpriseTag)+','+ 
    convert(nvarchar(10),@ImportVendeeEnterpriseTag)+','+ 
    convert(nvarchar(10),@ExportVendorEnterpriseTag)+','+ 
    convert(nvarchar(10),@ExportVendeeEnterpriseTag)+','+ 
    convert(nvarchar(10),@TransportationEnterpriseTag)+','+ 
    convert(nvarchar(10),@ConsigneeEnterpriseTag)+','+ 
    convert(nvarchar(10),@AssayEnterpriseTag)+','+ 
    convert(nvarchar(10),@SpotcheckEnterpriseTag)+','+ 
    convert(nvarchar(10),@TransportationEnterpriseIndex)+','+ 
    convert(nvarchar(10),@ConsigneeEnterpriseIndex)+')' 

Answer 5

Try:create procedure udsp_InsertOperationEnterprise 
@Name nvarchar(64), 
@ShortName nvarchar(32), 
@Address nvarchar(128), 
@AreaName nvarchar(16), 
@ArtificialPerson nvarchar(16), 
@ArtificialPersonTel nvarchar(32), 
@ArtificialPersonFax nvarchar(32), 
@Constituent nvarchar(16), 
@ConstituentTel nvarchar(32), 
@ConstituentFax nvarchar(32), 
@CableAddress nvarchar(32), 
@PostCode nvarchar(32), 
@AccountBank nvarchar(128), 
@BankAccount nvarchar(32), 
@RatePayingAccount nvarchar(32), 
@ImportVendorEnterpriseTag bit, 
@ImportVendeeEnterpriseTag bit, 
@ExportVendorEnterpriseTag bit, 
@ExportVendeeEnterpriseTag bit, 
@TransportationEnterpriseTag bit, 
@ConsigneeEnterpriseTag bit, 
@AssayEnterpriseTag bit, 
@SpotcheckEnterpriseTag bit, 
@TransportationEnterpriseName nvarchar(64), 
@ConsigneeEnterpriseName nvarchar(64) 
as 
        declare @Index smallint 
        declare @RecordAmount smallint 
set @RecordAmount=(select count(*) from udfdt_Department) 
        if (@RecordAmount=0) 
        begin 
set @Index=1 
  end 
else 
begin 
set @Index=(select max(df_Index) from udfdt_Department)+1 
end 
declare @AreaIndex smallint 
set @AreaIndex=isnull((select oeaf_Index from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName),0) 
declare @TransportationEnterpriseIndex smallint 
set @TransportationEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@TransportationEnterpriseName),0) 
declare @ConsigneeEnterpriseIndex smallint 
set @ConsigneeEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@ConsigneeEnterpriseName),0) 
        declare @InsertStatement nvarchar(4000) 
  set @InsertStatement= 
    convert(nvarchar(10),@Index)+ 
    ','''+@Name+''','+ 
    ','''+@ShortName+''','+ 
    ','''+@Address+''','+ 
                    convert(nvarchar(10),@AreaIndex)+','+ 
    ','''+@ArtificialPerson+''','+ 
    ','''+@ArtificialPersonTel+''','+ 
    ','''+@ArtificialPersonFax+''','+ 
    ','''+@Constituent+''','+ 
    ','''+@ConstituentTel+''','+ 
    ','''+@ConstituentFax+''','+ 
    ','''+@CableAddress+''','+ 
    ','''+@PostCode+''','+ 
    ','''+@AccountBank+''','+ 
    ','''+@BankAccount+''','+ 
    ','''+@RatePayingAccount+''','+ 
    convert(nvarchar(10),@ImportVendorEnterpriseTag)+','+ 
    convert(nvarchar(10),@ImportVendeeEnterpriseTag)+','+ 
    convert(nvarchar(10),@ExportVendorEnterpriseTag)+','+ 
    convert(nvarchar(10),@ExportVendeeEnterpriseTag)+','+ 
    convert(nvarchar(10),@TransportationEnterpriseTag)+','+ 
    convert(nvarchar(10),@ConsigneeEnterpriseTag)+','+ 
    convert(nvarchar(10),@AssayEnterpriseTag)+','+ 
    convert(nvarchar(10),@SpotcheckEnterpriseTag)+','+ 
    convert(nvarchar(10),@TransportationEnterpriseIndex)+','+ 
    convert(nvarchar(10),@ConsigneeEnterpriseIndex)+')' 
if exists(select 1 from udsdt_OperationEnterprise where oef_Name=@Name) 
raiserror('已存在同名记录',16,1) 
else 
execute('insert into udsdt_OperationEnterprise (oef_Index, 
    oef_Name, 
    oef_ShortName, 
    oef_Address, 
    oef_AreaIndex, 
    oef_ArtificialPerson, 
    oef_ArtificialPersonTel, 
    oef_ArtificialPersonFax, 
    oef_Constituent, 
    oef_ConstituentTel, 
    oef_ConstituentFax, 
    oef_CableAddress, 
    oef_PostCode, 
    oef_AccountBank, 
    oef_BankAccount, 
    oef_RatePayingAccount, 
    oef_ImportVendorEnterpriseTag, 
    oef_ImportVendeeEnterpriseTag, 
    oef_ExportVendorEnterpriseTag, 
    oef_ExportVendeeEnterpriseTag, 
                    oef_TransportationEnterpriseTag, 
    oef_ConsigneeEnterpriseTag, 
    oef_AssayEnterpriesTag, 
    oef_SpotcheckEnterpriseTag, 
    oef_TransportationEnterpriseIndex, 
    oef_ConsigneeEnterpriseIndex) values ('+@InsertStatement) go 

Answer 6

不用动态语句 
create procedure udsp_InsertOperationEnterprise 
@Name nvarchar(64), 
@ShortName nvarchar(32), 
@Address nvarchar(128), 
@AreaName nvarchar(16), 
@ArtificialPerson nvarchar(16), 
@ArtificialPersonTel nvarchar(32), 
@ArtificialPersonFax nvarchar(32), 
@Constituent nvarchar(16), 
@ConstituentTel nvarchar(32), 
@ConstituentFax nvarchar(32), 
@CableAddress nvarchar(32), 
@PostCode nvarchar(32), 
@AccountBank nvarchar(128), 
@BankAccount nvarchar(32), 
@RatePayingAccount nvarchar(32), 
@ImportVendorEnterpriseTag bit, 
@ImportVendeeEnterpriseTag bit, 
@ExportVendorEnterpriseTag bit, 
@ExportVendeeEnterpriseTag bit, 
@TransportationEnterpriseTag bit, 
@ConsigneeEnterpriseTag bit, 
@AssayEnterpriseTag bit, 
@SpotcheckEnterpriseTag bit, 
@TransportationEnterpriseName nvarchar(64), 
@ConsigneeEnterpriseName nvarchar(64) 
as 
        declare @Index smallint 
        declare @RecordAmount smallint 
set @RecordAmount=(select count(*) from udfdt_Department) 
        if (@RecordAmount=0) 
        begin 
set @Index=1 
  end 
else 
begin 
set @Index=(select max(df_Index) from udfdt_Department)+1 
end 
declare @AreaIndex smallint 
set @AreaIndex=isnull((select oeaf_Index from udfdt_OperationEnterpriseArea where oeaf_Name=@AreaName),0) 
declare @TransportationEnterpriseIndex smallint 
set @TransportationEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@TransportationEnterpriseName),0) 
declare @ConsigneeEnterpriseIndex smallint 
set @ConsigneeEnterpriseIndex=isnull((select a.oef_Index from udsdt_OperationEnterprise a where a.oef_Name=@ConsigneeEnterpriseName),0) if exists(select 1 from udsdt_OperationEnterprise where oef_Name=@Name) 
raiserror('已存在同名记录',16,1) 
else 
insert into udsdt_OperationEnterprise (oef_Index, 
    oef_Name, 
    oef_ShortName, 
    oef_Address, 
    oef_AreaIndex, 
    oef_ArtificialPerson, 
    oef_ArtificialPersonTel, 
    oef_ArtificialPersonFax, 
    oef_Constituent, 
    oef_ConstituentTel, 
    oef_ConstituentFax, 
    oef_CableAddress, 
    oef_PostCode, 
    oef_AccountBank, 
    oef_BankAccount, 
    oef_RatePayingAccount, 
    oef_ImportVendorEnterpriseTag, 
    oef_ImportVendeeEnterpriseTag, 
    oef_ExportVendorEnterpriseTag, 
    oef_ExportVendeeEnterpriseTag, 
                    oef_TransportationEnterpriseTag, 
    oef_ConsigneeEnterpriseTag, 
    oef_AssayEnterpriesTag, 
    oef_SpotcheckEnterpriseTag, 
    oef_TransportationEnterpriseIndex, 
    oef_ConsigneeEnterpriseIndex) values (
    @Index,
    @Name,
    @ShortName,
    @Address,
    @AreaIndex,
    @ArtificialPerson,
    @ArtificialPersonTel,
    @ArtificialPersonFax,
    @Constituent,
    @ConstituentTel,
    @ConstituentFax,
    @CableAddress,
    @PostCode,
    @AccountBank,
    @BankAccount,
    @RatePayingAccount,
    @ImportVendorEnterpriseTag,
    @ImportVendeeEnterpriseTag,
    @ExportVendorEnterpriseTag,
    @ExportVendeeEnterpriseTag,
    @TransportationEnterpriseTag,
    @ConsigneeEnterpriseTag,
    @AssayEnterpriseTag,
    @SpotcheckEnterpriseTag,
    @TransportationEnterpriseIndex,
    @ConsigneeEnterpriseIndex)
go 

Answer 7

declare @InsertStatement nvarchar(4000)
   set @InsertStatement='insert into udsdt_OperationEnterprise (oef_Index,
     oef_Name,
     oef_ShortName,
     oef_Address,
     oef_AreaIndex,
     oef_ArtificialPerson,
     oef_ArtificialPersonTel,
     oef_ArtificialPersonFax,
     oef_Constituent,
     oef_ConstituentTel,
     oef_ConstituentFax,
     oef_CableAddress,
     oef_PostCode,
     oef_AccountBank,
     oef_BankAccount,
     oef_RatePayingAccount,
     oef_ImportVendorEnterpriseTag,
     oef_ImportVendeeEnterpriseTag,
     oef_ExportVendorEnterpriseTag,
     oef_ExportVendeeEnterpriseTag,
                     oef_TransportationEnterpriseTag,
     oef_ConsigneeEnterpriseTag,
     oef_AssayEnterpriesTag,
     oef_SpotcheckEnterpriseTag,
     oef_TransportationEnterpriseIndex,
     oef_ConsigneeEnterpriseIndex) values ('+ 
     convert(nvarchar(10),@Index)+
     ','''+@Name+''''+
     ','''+@ShortName+''''+
     ','''+@Address+''''+
                     convert(nvarchar(10),@AreaIndex)+','+
     ','''+@ArtificialPerson+''''+
     ','''+@ArtificialPersonTel+''''+
     ','''+@ArtificialPersonFax+''''+
     ','''+@Constituent+''''+
     ','''+@ConstituentTel+''''+
     ','''+@ConstituentFax+''''+
     ','''+@CableAddress+''''+
     ','''+@PostCode+''''+
     ','''+@AccountBank+''''+
     ','''+@BankAccount+''''+
     ','''+@RatePayingAccount+''','+
     convert(nvarchar(10),@ImportVendorEnterpriseTag)+','+
                                                                     convert(nvarchar(10),@ImportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendorEnterpriseTag)+','+
     convert(nvarchar(10),@ExportVendeeEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseTag)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseTag)+','+
     convert(nvarchar(10),@AssayEnterpriseTag)+','+
     convert(nvarchar(10),@SpotcheckEnterpriseTag)+','+
     convert(nvarchar(10),@TransportationEnterpriseIndex)+','+
     convert(nvarchar(10),@ConsigneeEnterpriseIndex)+')'  
if exists(select 1 from udsdt_OperationEnterprise where oef_Name=@Name)
raiserror('已存在同名记录',16,1)
else
execute(@InsertStatement)goexec udsp_insertoperationenterprise '1212','1','','1','','','','','','','','','','','',1,1,1,1,1,1,1,1,'1','1'