Hello,
I have the following basic sp to truncate a table...
Create Procedure sp_Truncate_Address
@DBName varchar(30) = [AdventureWorks2014],
@TableName varchar(30) = [Person].[Address_Staging_A]
@SQL = 'TRUNCATE TABLE' + @DatabaseName + ' ' + @TableName
EXEC (@SQL)
...it does not like the two-part name, but I cant work out why or how to fix it, I get a red squiggle under the '.', or if I play around with it, under the [Person] schema name. Is there a knack to dealing with 2 and 3 part names in stored procs? Thank you.
Regards,
D.
You are going across DBs. So you need a 3-part name
@SQL = 'TRUNCATE TABLE ' + @DatabaseName + '.' + @ScemaName + '.' + @TableName
Gosh... don't use 3 part naming. Someday when someone moves either database or renames the further database, you'll hate yourself trying to find all the bloody 3 part naming you did.
My recommendation is to either write procs on the further database and call them to do things like TRUNCATE in that database or use synonyms but don't use 3 part naming in code.