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.
Create Procedure sp_Truncate_Family
AS
DECLARE @DBName varchar(30) = '<database name>'
Declare @TableName varchar(30) = 'dbo.<table_name>'
Declare @SQl Varchar(250) = 'TRUNCATE TABLE '+' ' + @DBName + '.'+ @TableName
EXEC (@SQl)
Modify your store proc as per the above script, it should work now.
I say again... don't use 3 part naming in code. You'll absolutely hate yourself someday for the previous reasons given.