SQL Injection in Code/Static SQL (T-SQL)

Question

Are parametrized static/code SQL statements subject to SQL injection attacks?

For example, let's say I have the following simplified stored procedure: Does the fact that I am passing the input @PSeries_desc mean I am subject to injection attacks if it is parameterized? Previously, this was a dynamic SQL statement and the code was executed using exec as opposed to sp_executesql So, it definitely was open to attacks.

CREATE procedure get_product_by_title
   @PSearchType int = NULL
  , @Pseries_desc varchar(40) = NULL
as
begin

declare
  @whereLikeBeg varchar(1)
, @whereLikeEnd varchar(1)

set @whereLikeBeg = ''
set @whereLikeEnd = ''

if @search_code = 'contains'
begin
  set @whereLikeBeg = '%'
  set @whereLikeEnd = '%'
end

if @search_code = 'starts_with'
begin
  set @whereLikeEnd = '%'
end

select
    distinct B.parent_product_id
    , B.parent_product_id
from
    tableA

where
    parent_product_id = child_product_id
    and product_title like @whereLikeBeg + @Pseries_desc + @whereLikeEnd
end

Answer 1

This code look safe to me...

Parametrized query is not the only way to protect yourself from SQL-injection attacks but it's probably the simplest and safest way to do it.

And even if you forget about the sql-injection attacks, building query dynamically is not a good practice, especially when you are working with strings because they might contains SQL reserved words / characters that will have an impact on your query.