Insufficient number of arguments for sp_rename in synapse stored procedure

Question

I have this stored procedure in Synapse dedicated sql pool that is renaming a staging table to a main table.

ALTER PROCEDURE [dbo].[RenameStagingTableToDedicatedTable]
(

    @DedicatedTableName VARCHAR(MAX)
)
AS
BEGIN
    -- Check if the Dedicated table exists
    IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'U' AND name = @DedicatedTableName)
    BEGIN
        -- Rename the staging table to the Dedicated table
        EXEC sp_rename 'gold.Service_Staging', 'gold.Service'
 
        -- Drop the existing Dedicated table
        EXEC ('DROP TABLE ' + @DedicatedTableName)
    END
    ELSE
    BEGIN
        -- The Dedicated table does not exist, so rename the staging table to the Dedicated table
        EXEC sp_rename 'gold.Service_Staging', 'gold.Service'
 
    END
END
GO

When executing this stored procedure, I get the following error:

An insufficient number of arguments were supplied for the procedure or function sp_rename

Any ideas why?

Answer 1

sp_rename is in preview in Azure Synapse Analytics:

Applies to: Azure Synapse Analytics

In sp_rename (preview) for Azure Synapse Analytics, COLUMN is a mandatory value specifying that the object type to be renamed is a column, and must always be included in the sp_rename statement. A column can only be renamed if it's not a distribution column.

So it appears you can only rename columns at present (which seems a strange restriction).

Also, it seems that you might have an XY problem. Staging tables are usually created once and truncated, or simply created and dropped as required.