Sumeet says:
Sir,
there is a query below. In this query @colName is the variable for column names & @colData is the variable that hold the data for each column.
In database all the columns are decimal[18,2] therefore the data in @colData is decimal[18,2].
I have multiple columns in @colName and there respective data in @colData for single row.
When i am executing the code then it's giving me error as cannot convert string to int.
How should I save single row for all columns at once ?....
|
1
2
3
4
|
declare @query varchar(MAX);
set @query = 'INSERT INTO TransactionTab (HeadCode,'+@colName+')
VALUES('+ CONVERT(int, @MajorHeadCode)+', '+ CONVERT(decimal(18,2),@colData)+')'
EXEC(@query)
|
As it’s your dynamic query which get sets by different parameter dynamically and executed so you need to maintain the query string as in string format there is concatenation of string with integer and decimal variables because of this it throwing error.
If it was simple insert without dynamic query then it will get executed but for dynamic query you always need to convert each variable as string type so concatenate string will not throw any error.
Check the below query and implement it by your own logic which will solve your problem.
SQL
DECLARE @query VARCHAR(MAX);
SET @query = 'INSERT INTO TransactionTab (HeadCode,'+ @colName+')
VALUES('+CONVERT(VARCHAR(MAX), CONVERT(INT, @MajorHeadCode)) +', '+ CONVERT(VARCHAR(MAX),CONVERT(DECIMAL(18,2),@colData))+')'
