I need to build values based on some variables in this stored procedure and can't get it to work.

declare
@CRD_PRSN_SUFFIX varchar(32),
@name_id int

My sql select is very basic

create table #results
insert #results
select
name_id,
product,
null(value of @CRD_PRSN_SUFFIX goes here)
from
table A,
table B

So from that result set I need to capture the suffix for each name_id and a stored procedure must be run to get the result. Can I run my variables in there where the @name_id = a.name_id ? Then run an update in my #results table as it is compiling? Or I could build another temp temp with the results of this stored procedure and map to my #results table. Which method would be best?

This is the stored procedure where the suffix is identified for each name_id:

EXECUTE stored_procedure_name_here @name_id,NULL, NULL, NULL, ',B', 10, 'Y', 'A', 0, 'S', @CRD_PRSN_SUFFIX