Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

T SQL variable showing incorrect syntax

DECLARE @tablename VARCHAR(100) = 'House';


IF OBJECT_ID(@tablename, N'U') IS NOT NULL
BEGIN
    IF EXISTS(SELECT 1 FROM @tablename)
    BEGIN
        PRINT 'Table already exists and has data, not dropping it.'
    END
    ELSE
    BEGIN
        DROP TABLE @tablename
        PRINT 'Table dropped successfully.'
    END
END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

Please correct it for SQL Server

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

You can try this:

DECLARE @tablename NVARCHAR(128) = 'House';


IF OBJECT_ID(@tablename, N'U') IS NOT NULL
BEGIN

    DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

    SET @DynamicTSQLStatement = N'

    IF EXISTS(SELECT 1 FROM ' + @tablename + ')
    BEGIN
        PRINT ''Table already exists and has data, not dropping it.''
    END
    ELSE
    BEGIN
        DROP TABLE ' + @tablename + '
        PRINT ''Table dropped successfully.''
    END

    ';

    EXEC sp_executesql @DynamicTSQLStatement;

END
ELSE
BEGIN
    PRINT 'Table does not exist.'
END

Also, it would be better to pass and the schema of the table.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading