How to pass int,float etc. from dynamic string INSERT INTO function in SQL Server

Question

I made a dynamic table name but table parameters want to string type all params.

I did cast all parameters but I get an error. I tried all the ways for instance +70+ or N'70', Cast(70 int), etc but I couldn't do it Do you have an idea for this question

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```

You should **not** be concatenating parameters, this is open to SQL Injection - you should *read the documentation* for sp_executesql and *parameterise* your query. you also appear to be trying to concatenate integer literals, I have no idea why but this will fail. — Stu, Feb 12 '22 at 11:46
If you use the "+" operator, the engine will determine what to do (add or concat) based on the datatypes on either side. Since any operator requires the same datatype for both sides, a conversion is done implicitly if YOU don't explicitly convert. then implicit conversion is done based on [datatype precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15). End result is addition which fails. — SMor, Feb 12 '22 at 11:59
`SET @TABLENAME = 'LG_'+@FIRMNUMBER+'_'+@PERIODNUMBER+'_CLFICHE'` If this is your design, you will need much more skill in tsql to work effectively in this environment. — SMor, Feb 12 '22 at 12:01

score 1 · Accepted Answer · answered Feb 12 '22 at 12:01

1

This is how you do it.

Int , Float values should be concatenated to dynamic query only after casting it as varchar. That is the problem in your query above here ('+Cast(@BRANCH as int)+'. This will not work because you are trying to concatenate a int value to varchar string.

Varchar, text, String , date or datetime values should be concatenated such that these values should be enclosed in single quotes.

To demonstrate, I took one column each of int , float and date type in my table below.

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)

Since I printed the query string before executing it, you see the insert statement below which is getting executed using exec.

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

answered Feb 12 '22 at 12:01

PankajSanwal

956
7
14

thank you for the answer I have a last question to you. Create table temp( name varchar(200), ColumnA int, ColumnB float, ColumnC date,ColumnD int) set @sql = 'Insert into temp (name,ColumnA, ColumnB, ColumnC) values ('''+@name+''','+cast(@colA as varchar)+','+cast(@colB as varchar)+','''+convert(varchar(10),@colC,120)+''',0)' print(@sql) exec(@sql) How can I pass '0' as default value ? it throws error – heker Feb 12 '22 at 12:37
There are 2 ways. You can either add default to any column at table, but if altering table is not an option then use isnull function as cast( isnull( @colA,0) as varchar ) – PankajSanwal Feb 12 '22 at 13:54
1

This has SQL injection all over it. What happens if [my name is `Robert'); DROP TABLE Students;--`](https://bobby-tables.com/)? – Charlieface Feb 12 '22 at 21:28
('''+@name+''','+cast(@colA as varchar)+','+cast(@colB as varchar)+','''+convert(varchar(10),@colC,120)+''') There are all syntaxs PankajSanwal's answer . I edited my store prosedure last of all There isn't just one thing .I used '''' expression as string empty – heker Feb 13 '22 at 11:09

How to pass int,float etc. from dynamic string INSERT INTO function in SQL Server

1 Answers1