To check whether the table exists or not dynamically

DECLARE @DROP VARCHAR(MAX)
DECLARE @TAB VARCHAR(MAX)='Employees'
DECLARE @CREATE VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)

--IF OBJECT_ID('DBO.'[email protected],'U') IS NOT NULL
--BEGIN
IF (EXISTS (SELECT 1 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = 'dbo' 
              AND TABLE_NAME = @TAB))
BEGIN
    SET @DROP = N'DROP TABLE '[email protected]
    EXEC(@TAB)
END

SET @CREATE= 'CREATE TABLE '[email protected]+
    '(
        ID INT
        ,NAME VARCHAR(50)
        ,PHONE VARCHAR(25)
        ,ADDRESS VARCHAR(100)
    )'

EXEC(@CREATE)
SET @SELECT='SELECT * FROM '[email protected]
EXEC(@SELECT)

I get an error, why?

Msg 2809, Level 16, State 1, Line 1
The request for procedure ‘Employees’ failed because ‘Employees’ is a table object.

Answer

You did only one mistake instead of using EXEC(@TAB) You should use EXEC(@DROP)

DECLARE @DROP VARCHAR(MAX)
DECLARE @TAB VARCHAR(MAX)='Employees'
DECLARE @CREATE VARCHAR(MAX)
DECLARE @SELECT VARCHAR(MAX)
--IF OBJECT_ID('DBO.'[email protected],'U') IS NOT NULL
--BEGIN
IF 
(EXISTS 
    (
    SELECT 
        1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'dbo' 
    AND  TABLE_NAME = @TAB
    )
)
BEGIN
    SET @DROP=N'DROP TABLE '[email protected]
    print @DROP
    EXEC(@DROP)
END
SET @CREATE=
    'CREATE TABLE '[email protected]+
    '(
        ID INT
        ,NAME VARCHAR(50)
        ,PHONE VARCHAR(25)
        ,ADDRESS VARCHAR(100)
    )'
EXEC(@CREATE)
SET @SELECT='SELECT * FROM '[email protected]
EXEC(@SELECT)

Leave a Reply

Your email address will not be published. Required fields are marked *