SQL Server T SQL variable showing incorrect syntax [duplicate]

3phpmpom 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(142)

This question already has answers here:

A table name as a variable (10 answers)
Closed 5 days ago.

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

sql-server

来源：https://stackoverflow.com/questions/75541231/t-sql-variable-showing-incorrect-syntax

1条答案

按热度按时间

hs1ihplo1#

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.

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server T SQL variable showing incorrect syntax [duplicate]

1条答案

相关问题

热门标签

最新问答