• DesNorton - Friday, October 20, 2017 12:28 AM

    Duran - Thursday, October 19, 2017 11:20 PM

    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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)