Post reply

Help me with Dynamic SQL Syntax, I am getting an error message ..???

  • May 22, 2019 at 5:17 pm

    #3646254

    It gives me an error message as follows: I am trying to backup a table using the SELECT * INTO ....

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '@BkTblName'.
    DECLARE @BkTblName  varchar(100);
    DECLARE @SQLString nvarchar(500);  
    DECLARE @ParmDefinition nvarchar(500);  

    SELECT @BkTblName = 'HRAHistory_20190521_XX';
    SELECT @ParmDefinition = N' @BkTblName VARCHAR(100) ';  
    SELECT @SQLString = N'Select * INTO @BkTblName FROM  GHP_HRA.HRAHistory_20190521  ';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @BkTblName=@BkTblName ;

  • May 22, 2019 at 5:59 pm

    #3646261

    I don't think it works like this. Parameter is what you put on the WHERE clause. If  you want change table name, concatenate:

    SELECT @SQLString = N'Select * INTO ' + @BkTblName + ' FROM  GHP_HRA.HRAHistory_20190521  ';

    --Vadim R.

  • May 22, 2019 at 6:05 pm

    #3646264

    Cool, It worked! Thanks

  • May 22, 2019 at 9:08 pm

    #3646306

    rVadim wrote:

    I don't think it works like this. Parameter is what you put on the WHERE clause. If  you want change table name, concatenate:

    SELECT @SQLString = N'Select * INTO ' + @BkTblName + ' FROM  GHP_HRA.HRAHistory_20190521  ';

    I don't recommend that on it's own, it'll suffer from injection. Quote your objects when using Dynamic SQL:

    SET @SQLString = N'SELECT * INTO ' + QUOTENAME(@BkTblName) + N' FROM  GHP_HRA.HRAHistory_20190521;';

    Ideally, as well, you should be declaring your schema name in the statement as well (which as it isn't specified, I assume is dbo).

    You could even go further and check the validity of the table, which I explain how to do here: Dos and Don'ts of Dynamic SQL.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply