Using Dynamic SQL With Trigger And Stored Procedures

Hi!

I have an integration code write in T-SQL. It´s a TRIGGER that when some data is INSERTED on a specific table, verify the first caracter of a nvarchar on the column named "idCli", and depending on their value, call one specific stored procedure that will execute some data modifications to fit on other table on a diferent database.

Each client of mine can have only one table that start the trigger on APP1, but can have many instances of SQL for different codes.

Until now, what we do is:

Find how many different databases (and their names) a specific client have to APP2 and write a specific stored procedure for each database, using the names (that are always different...). We use a template of course, but this don't change the fact the we must correct many times the database name on the different stored procedures.

This increse the time and chance of errors on installing the system.

The first way we think for solve this question is using dinamic sql, like this code:





Code Snippet
CREATE TRIGGER T01
ON [dbo].[table1]
FOR INSERT, UPDATE
AS
-- some code that put values in @v1 and @V2...
IF @v1 = 1
EXEC fct ('DB1..Tabela1', @V2)
ELSE
EXEC fct ('DB2..Tabela1', @V2)
GO

CREATE PROCEDURE fct (@table_name nvarchar(50), @valor int)
AS
EXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')
GO






This type of code has the advantege (we think) to permit us change only the TRIGGER, and use always the same number of procedures on install.

Is there any security problem to do this type of code?
Even if the @table_name and @valor are determined by the program?

In case of yes, how can I do something like this, or, if this is not possible, how can I "automate" the creation of the procedures with a variable number of choices (like 2 different tables for client A, 5 for client B, etc)?

Thanks in advance