The code in solution 1 is vulnerable to
SQL Injection[
^].
NEVER use string concatenation/interpolation to build a SQL query.
ALWAYS use a parameterized query.
In the case of dynamic SQL within a stored procedure, that means using
sp_executesql[
^] with proper parameters.
DECLARE @sql_Query nvarchar(8000);
SET @sql_Query = N'Update TPCA set ';
If @POL_NBR Is Not Null And @POL_NBR <> ''
Begin
SET @sql_Query = @sql_Query + N' TPCA.POL_NBR = @POL_NBR';
End;
...
EXEC sp_executesql @sql_Query,
N'@tbl_pol_id int, @LOB varchar (50), @POL_NBR varchar(50)',
@tbl_pol_id = @tbl_pol_id,
@LOB = @LOB,
@POL_NBR = @POLNBR;