Dynamic SQL with out parameters

Question

Hi

I am trying to convert a normal STP to a partially dynamic SQL I need the dynamic part to set value to a parameter, which I would later use in my non-dynamic part of the STP.

when I print it in the inside of the dynamic @stmt it's OK but outside it is empty

what am I missing?

--------------------------------------------------------------constructing statement

set @stmt = 'set @stringActors = cast((select actors.actor_id
 '+@from_clause+' '
 +@where_clause+' '
 +'for xml raw,type) as varchar(max)) print @stringActors'

print @stmt

--------------------------------------------------------------execution

EXECUTE sp_executesql @stmt,N'
@stringActors varchar(4000) output
,@user_actor bigint
,@internal_search_actor varchar(30)
,@internal_search_route_manager     varchar(30)
,@internal_search_terminal_id  varchar(30)
,@ACTOR_TYPE_INSTITUTE int 
,@ACTOR_TYPE_LOCATION int'
,@stringActors =@stringActors
,@user_actor=@user_actor
,@internal_search_actor=@internal_search_actor
,@internal_search_route_manager=@internal_search_route_manager
,@internal_search_terminal_id=@internal_search_terminal_id
,@ACTOR_TYPE_INSTITUTE=@ACTOR_TYPE_INSTITUTE
,@ACTOR_TYPE_LOCATION=@ACTOR_TYPE_LOCATION

-----------------------------------------------------------------rest of STP (not dynamic) print @stringActors

Answer 1

OUTPUT has to be specified both when declaring and assigning an OUTPUT parameter, so I would say the problem is

,@stringActors =@stringActors
,@user_actor=@user_actor

should be

,@stringActors =@stringActors OUTPUT
,@user_actor=@user_actor

Also, check out this MSDN