如何将int、from等从动态字符串插入传递到Server中的函数中

Question

我创建了一个动态表名，但是表参数希望字符串类型为所有params。

我确实转换了所有参数，但是我得到了一个错误。我试过所有的方法，比如+70+或者N'70'，Cast(70 int)等等，但是我做不到，你对这个问题有什么想法吗？

CREATE PROCEDURE TEST    
    @FIRMNUMBER VARCHAR(10),  
    @PERIODNUMBER VARCHAR(10),      
    @FCHNO VARCHAR(16),
    @DCDNO VARCHAR(16), 
    @DATE VARCHAR(20),
    @CLINTREF INT, 
    @SLSMNREF INT,
    @BRANCH INT,
    @GENEXP1 VARCHAR(20), 
    @GENEXP2 VARCHAR(20),
    @CREDIT FLOAT 
AS    
    DECLARE @Sql NVARCHAR(MAX);  
    DECLARE @TABLENAME NVARCHAR(40);  

    SET @FIRMNUMBER = REPLACE(STR(@FIRMNUMBER, 3), SPACE(1), '0')
    SET @PERIODNUMBER = REPLACE(STR(@PERIODNUMBER, 2), SPACE(1), '0')

    SET @TABLENAME = 'LG_' + @FIRMNUMBER + '_' + @PERIODNUMBER + '_CLFICHE'  

    SET @Sql = N'INSERT INTO ' + QUOTENAME(@TABLENAME) + '( FICHENO, DATE_, DOCODE, TRCODE, SPECCODE, CYPHCODE, BRANCH, DEPARTMENT, GENEXP1, GENEXP2, GENEXP3, GENEXP4, GENEXP5, GENEXP6, DEBIT, CREDIT, REPDEBIT,    
    
REPCREDIT, CAPIBLOCK_CREATEDBY, CAPIBLOCK_CREADEDDATE, CAPIBLOCK_CREATEDHOUR, CAPIBLOCK_CREATEDMIN, CAPIBLOCK_CREATEDSEC, CAPIBLOCK_MODIFIEDBY,    
    
CAPIBLOCK_MODIFIEDDATE, CAPIBLOCK_MODIFIEDHOUR, CAPIBLOCK_MODIFIEDMIN, CAPIBLOCK_MODIFIEDSEC, ACCOUNTED, INVOREF, CASHACCREF, CASHCENREF, PRINTCNT, CANCELLED,    
    
CANCELLEDACC, ACCFICHEREF, GENEXCTYP, LINEEXCTYP, TEXTINC, SITEID, RECSTATUS, ORGLOGICREF, WFSTATUS, TIME, CLCARDREF, BANKACCREF, BNACCREF, BNCENTERREF, TRADINGGRP,    
    
POSCOMMACCREF, POSCOMMCENREF, POINTCOMMACCREF, POINTCOMMCENREF, PROJECTREF, STATUS, WFLOWCRDREF, ORGLOGOID, AFFECTCOLLATRL, GRPFIRMTRANS, AFFECTRISK,    
    
POSTERMINALNR, POSTERMINALNUM, APPROVE, APPROVEDATE, SALESMANREF, CSTRANSREF, DOCDATE, GUID, DEVIR, PRINTDATE, FOREXIM, TYPECODE, EINVOICE, HOUR_, MINUTE_, DEDUCTCODE    
    
,ELECTDOC, NOTIFYCRDREF, GIBACCFICHEREF    
    
)   
   
VALUES ( '+@FCHNO+' ,'+@DATE+' , '' , 70 , '' , '' ,'+Cast(@BRANCH as int)+' , 0,'+@GENEXP1+','+@GENEXP2+', '' , '', '' ,

'', 0,'+@CREDIT +', 0,'+@CREDIT+', '+1+', '+GETDATE()+', 0, 32, 34, 0, '',0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0,

0, 1, 0, 0, '+1704971+', '+2012+', 0, 0, 0, '', 0, 0, 0,

0, 0, 0, 0, '', 0, 0 , '+1+', 0, '', 0, '','+@SLSMNREF+', 0,

'','+Cast(NEWID() as nvarchar(50))+', 0 , '', 0, '', 0, 0, 0, ''

, 0,0, 0

)'

EXEC sp_executesql @Sql   
  

exec TEST '7','9','073','BLGNO' ,'2022-2-2',14  ,2  ,0  ,'ACIK1','ACIK2', 120.23```

Answer 1

你就是这样做的。

Int，只有在将浮点值转换为varchar之后，才能将其连接到动态查询。这就是上面查询中的问题(‘+Cast(@分支为int)+’“。这将无法工作，因为您正在尝试将int值连接到varchar字符串。

应该将Varchar、text、String、date或datetime值连接起来，以便将这些值用单引号括起来。

为了演示，我在下面的表中分别输入了int、float和date的一列。

Create table temp( name varchar(200), ColumnA int, ColumnB float, ColumnC date)

declare @sql varchar(2000), @name varchar(10)='xyz', @colA int = 1, @colB float = 1.2, @colC datetime = getdate()

set @sql = 'Insert into temp (name,ColumnA, ColumnB, ColumnC) values ('''+@name+''','+cast(@colA as varchar)+','+cast(@colB as varchar)+','''+convert(varchar(10),@colC,120)+''')'
print(@sql)
exec(@sql)

由于我在执行查询字符串之前打印了查询字符串，因此您可以看到下面的insert语句，该语句正在使用exec执行。

Insert into temp (name,ColumnA, ColumnB, ColumnC) values ('xyz',1,1.2,'2022-02-12')
1 rows affected