Hey,
The below link might help :
https://learn.microsoft.com/en-us/answers/questions/31693/how-to-pass-dynamic-parameter-in-store-procedure-u.html
So the above example is w.r.t stored procedure activities.
You can also leverage dynamic content to execute SP via lookup activity like:
exec SPname '@{pipeline.parameter()}' somewhat like this.