0

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```
Improve this question
3
  • 3
    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. Commented Feb 12, 2022 at 11:46
  • 1
    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. End result is addition which fails. Commented Feb 12, 2022 at 11:59
  • 1
    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. Commented Feb 12, 2022 at 12:01

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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
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 )
This has SQL injection all over it. What happens if my name is Robert'); DROP TABLE Students;--?
('''+@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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.