Error "INCORRECT SYNTAX NEAR '@nombretbl'" trying to query with dynamic table name

Question

Error "INCORRECT SYNTAX NEAR '@nombretbl'" trying to query with dynamic table name

Navigation

#1 by (2 votes)

1

Hello Friends, I'm trying to generate several tables in Sql, which will come from a base table.

Example: I have a BONUS table that can contain more or less than 4 million records; and I have a CONTEO_CUENTA table, where are the amounts of records by accounts (field auxc_count of tbl BONUS); and what I try to do is to separate into tables (WITH THE NAME OF THE ACCOUNT OR ITEM) the BONUS information depending on the number of records in AUXC_CUENTA.

Declare @suma int ,@conteo1 int, @item1 int, @item2 int, @nombretbl varchar(100)
Set @conteo1=600000
Set @suma=(SELECT SUM(conteo) from CONTEO_CUENTA where conteo<@conteo1)
set @item1=1 

IF @suma<4000000
    BEGIN
        SELECT BONUS.* INTO CUENTA_0 FROM BONUS WHERE AUXC_CUENTA IN(SELECT AUXC_CUENTA FROM CONTEO_CUENTA WHERE CONTEO<@conteo1) 
        set @item2=(select top 1 dbo.CONTEO_CUENTA.ITEM from CONTEO_CUENTA where CONTEO<@conteo1 order by ITEM asc) 
        While (@item1<=@item2)
        Begin
        Set @nombretbl =  'CUENTA_'+'['+CAST(@ITEM1 AS VARCHAR(30))+']' --(select auxc_cuenta from CONTEO_CUENTA where ITEM = @item1)
        SELECT BONUS.* INTO @nombretbl FROM dbo.BONUS WHERE AUXC_CUENTA=(select dbo.CONTEO_CUENTA.AUXC_CUENTA FROM CONTEO_CUENTA WHERE ITEM=@item1)
        END
        set @item1 = @item1 + 1
        End
Else
    Begin
        set @conteo1=500000
        SELECT BONUS.* INTO CUENTA_0 FROM BONUS WHERE AUXC_CUENTA IN(SELECT AUXC_CUENTA FROM CONTEO_CUENTA WHERE CONTEO<@conteo1) 
        set @item2=(select top 1 dbo.CONTEO_CUENTA.ITEM from CONTEO_CUENTA where CONTEO<@conteo1 order by item asc)
        While (@item1<=@item2)
        Begin
        Set @nombretbl= (select auxc_cuenta from CONTEO_CUENTA where ITEM = @item1)
        SELECT BONUS.* INTO @nombretbl FROM BONUS WHERE AUXC_CUENTA=(select dbo.CONTEO_CUENTA.AUXC_CUENTA FROM CONTEO_CUENTA WHERE ITEM=@item1)
        set @item1 = @item1 + 1
        End
    End
    select count(*) from CUENTA_0

But there is a problem with the variable @nombretbl , I get it:

INCORRECT SYNTAX NEAR '@nombretbl'

sql sql-server

asked by llogo 28.12.2017 в 17:32

source

1 answer

I can not update a field in my MySql database from Java ERROR mysql_num_rows () expects parameter 1 to be resource, boolean given

score 2 · Answer 1

The problem is when you do:

SELECT BONUS.* INTO @nombretbl FROM ...

You can not use a variable where a table name corresponds.

To do what you want, you must use dynamic SQL:

declare @dynamicSQL nvarchar(200);

...

set @dynamicSQL = 'SELECT BONUS.* INTO ' + @nombretbl + ' FROM dbo.BONUS WHERE AUXC_CUENTA=(select dbo.CONTEO_CUENTA.AUXC_CUENTA FROM CONTEO_CUENTA WHERE ITEM=@item1)';
exec sp_executesql @dynamicSQL, N'@item1 int', @item1 = @item1;