0

I have 2 stored proc.

The first one receive as input parameter some variables and one of them is XML.

Inside this proc i call another proc but dynamically like that. This proc handle the XML to insert it in the DB.

SET @SQL = 'EXEC [' + @ServerIp + '].[List_' + CAST(@AccountId AS VARCHAR) + '].[dbo].[psu_proc_name] @ParameterId = ' + CAST(@ParameterId AS VARCHAR) + ',@MappingFields = ' + @MappingFields

@MappingFields is the name of the XML variable in the first proc and also in the second proc (called dynamically)

I manage the treatment of the XML in the second proc (dynamic) but i don't know how to pass the parameter as XML to the second proc.

Thanks

2 Answers2

1

The best way to do this is to use the sp_executesql stored procedure instead of using the simle exec command. See the not duplicate, but very clear examples in the question EXEC sp_executesql with multiple parameters for easy to understand details.

Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28
0

i've tried something like that : 

SET @SQL = N'EXEC [' + @ServerIp + '].[List_' + CAST(@AccountId AS VARCHAR) + '].[dbo].[psu_opensystem_field_mapping_add]'
EXEC sp_executeSQL @SQL, N'@ParameterId INT, @MappingFields XML' , @ParameterId, @MappingFields

But it says that 

Procedure or function 'psu_opensystem_field_mapping_add' expects parameter '@ParameterId', which was not supplied
  • Remove the EXEC statement from your SQL string. Your command should be `EXEC sp_executeSQL @SQL, N'@ParameterId INT, @MappingFields XML' , @ParameterId, @MappingFields`, and the @SQL variable should contain `N'[' + @ServerIp + '].[List_' + CAST(@AccountId AS VARCHAR) + '].[dbo].[psu_opensystem_field_mapping_add]' – Laughing Vergil Oct 11 '19 at 16:14