How to pass parameter values to OBJECTID in SQL Server?-CodePudding

I am writing a stored procedure with DB name as input parameter. I am creating some physical tables dynamically. If a table already exists then I need to delete the Table.

DECLARE @TargetDBName NVARCHAR(100) = 'PRD_Inventory'
DECLARE @TargetSchema NVARCHAR(100) = 'usr'

Current Query

IF OBJECT_ID('TempUserData') IS NOT NULL DROP TABLE TempUserData

Expected format:

IF OBJECT_ID('@[email protected]') IS NOT NULL DROP TABLE @[email protected]

CodePudding user response：

You would need to build your SQL dynamically for this. Note also when defining variables that refer to object names you should use the sysname type.

The following works on currently supported SQL Servers, it should also work on SQL Server 2008 but untested of course.

declare @TargetDBName sysname = 'PRD_Inventory',
        @TargetSchema sysname = 'usr', 
        @sql nvarchar(max);

set @sql = 'if exists (
  select * from '   QuoteName(@TargetDBName)   '.sys.objects o
  join '   QuoteName(@TargetDBName)   '.sys.schemas s on s.schema_id = o.schema_id
  where o.name = ''TempUserData'' and s.name = '''   @TargetSchema   '''
) drop table '  QuoteName(@TargetDBName)   '.'   QuoteName(@TargetSchema)   '.TempUserData;'

exec (@sql);

CodePudding user response：

its may be work :

DECLARE @TargetDBName NVARCHAR(100) = 'PRD_Inventory'
DECLARE @TargetSchema NVARCHAR(100) = 'usr'
DECLARE @TableName NVARCHAR(100) = 'tablename'
DECLARE @TableNameWithShema NVARCHAR(250) = @TargetDBName  '.' @TargetSchema '.' @TableName ;

IF OBJECT_ID(@TableNameWithShema) IS NOT NULL DROP TABLE @TableNameWithShema