sql - Dynamic query is not giving desired result -
i creating dynamic query updating column if exists in table. when update query running giving error. following procedure
create procedure spr_updatecolumnwithtotalprice @id int begin if not exists ( select * information_schema.columns table_name = 'product' , column_name = 'productsellingprice' ) begin declare @sellingprice varchar(max) set @sellingprice = 'alter table product add productsellingprice int' exec(@sellingprice) print 'table altered' end else begin set @sellingprice = 'update product set productsellingprice = ((unitprice * [gstrate%]/100) + unitprice ) productid = @id' exec(@sellingprice) print 'table updated' end
end
i getting following result :-
conversion failed when converting varchar value 'update product set productsellingprice = ((unitprice * [gstrate%]/100) + unitprice ) productid = @id' data type int.
please , in advance
try instead:
set @sellingprice = 'update product set productsellingprice = ((unitprice * [gstrate%]/100) + unitprice ) productid = '+ cast(@id varchar(13))
you can't use +
concatenate int string, since sql server try implicitly convert string int.
why need explicitly convert int string.
Comments
Post a Comment