Creating and using synonyms in a stored procedure

Question

I am creating a stored procedure that will be called from one database but needs to access tables in other databases. We have a different database for each of our clients. I'm trying to create a synonym dynamically based on parameters:

DECLARE @SQL varchar(150)
SET @SQL = 'CREATE SYNONYM synATClaimMatched FOR ' + ... <params> ...
EXEC @SQL

But this results in the following error:

Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure ''.".

Does anybody know why I'm getting this error and how to fix it?

Answer 1

Your problem really has nothing to do with synonyms or the stored procedure, just invalid syntax for dynamic SQL.

Use:

EXEC sp_executesql @sql;

-- or

EXEC(@sql);

Though I prefer the first one. Here's why:

• Bad Habits to Kick : Using EXEC() instead of sp_executesql