SQL Server - Dynamic Table Name as Variable

Question

I have been having problems trying to create tables with dynamic name. I keep getting these errors:

Msg 102, Level 15, State 1, Procedure SP_SearchTables, Line 159
Incorrect syntax near '@tablename'.

Msg 1087, Level 15, State 2, Procedure SP_SearchTables, Line 165
Must declare the table variable "@tablename".

Msg 1087, Level 15, State 2, Procedure SP_SearchTables, Line 171
Must declare the table variable "@tablename".

As you can see in the stored procedure, before this changes I had SearchTMP instead @tablename, but I don't want to give a fixed name, I want to give a dynamic name. With that in mind, I gave a name and some random numbers, but it keeps giving me the errors I showed previously, could you please help me?

I will post my stored procedure.

ALTER PROCEDURE [dbo].[SearchTables] 
    @SearchStr NVARCHAR(60),
    @GenerateSQLOnly BIT = 0, 
    @SchemaNames VARCHAR(500) ='%' 
AS 
    SET NOCOUNT ON 

    DECLARE @MatchFound BIT 

    SELECT @MatchFound = 0 

    DECLARE @CheckTableNames TABLE (Schemaname sysname, Tablename sysname) 
    DECLARE @SearchStringTbl TABLE (SearchString VARCHAR(500)) 

    DECLARE @SQLTbl TABLE 
            ( 
                Tablename SYSNAME,
                WHEREClause VARCHAR(MAX),
                SQLStatement VARCHAR(MAX),
                Execstatus BIT  
            ) 

    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableParamSQL VARCHAR(MAX) 
    DECLARE @SchemaParamSQL VARCHAR(MAX) 
    DECLARE @TblSQL VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
    DECLARE @ErrMsg VARCHAR(100) 

    IF LTRIM(RTRIM(@SchemaNames)) ='' 
    BEGIN 
        SELECT @SchemaNames = '%' 
    END 

    IF CHARINDEX(',',@SchemaNames) > 0  
        SELECT @SchemaParamSQL = 'SELECT ''' + REPLACE(@SchemaNames,',','''as SchemaName UNION SELECT ''') + '''' 
    ELSE 
        SELECT @SchemaParamSQL = 'SELECT ''' + @SchemaNames + ''' as SchemaName ' 

    SELECT @TblSQL = 'SELECT SCh.NAME,T.NAME 
                      FROM SYS.TABLES T 
                      JOIN SYS.SCHEMAS SCh ON SCh.SCHEMA_ID = T.SCHEMA_ID 
                      INNER JOIN [DynaForms].[dbo].[Enums_Tables] et ON (et.Id = T.NAME COLLATE Latin1_General_CI_AS)  '

    INSERT INTO @CheckTableNames (Schemaname, Tablename) 
        EXEC (@TblSQL) 

    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 

    IF LTRIM(RTRIM(@SearchStr)) ='' 
    BEGIN 
        SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter' 
        PRINT @ErrMsg 
        RETURN 
    END 
    ELSE 
    BEGIN  
        SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#') 
        SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#') 

        SELECT @SearchStr = REPLACE(@SearchStr,'''','''''') 

        SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + '''' 

        INSERT INTO @SearchStringTbl (SearchString) 
            EXEC(@SQL) 

        UPDATE @SearchStringTbl 
        SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',') 
    END 

    INSERT INTO @SQLTbl (Tablename, WHEREClause) 
        SELECT 
            QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
            (SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10) 
             FROM SYS.columns SC 
             JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id 
                                AND STy.user_type_id =SC.user_type_id 
             CROSS JOIN @SearchStringTbl SearchSTR 
             WHERE STY.name IN ('varchar', 'char', 'nvarchar', 'nchar', 'text') 
               AND SC.object_id = ST.object_id 
             ORDER BY SC.name 
             FOR XML PATH('') 
            ) 
        FROM  
            SYS.tables ST 
        JOIN 
            @CheckTableNames chktbls ON chktbls.Tablename = ST.name  
        JOIN 
            SYS.schemas SCh ON ST.schema_id = SCh.schema_id 
                            AND Sch.name = chktbls.Schemaname 
        WHERE 
            ST.name <> 'SearchTMP' 
        GROUP BY 
            ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ; 

    DECLARE @numbers VARCHAR(MAX)
    SET @numbers = CONVERT(NUMERIC(12, 0), RAND() * 899999999999) + 100000000000

    DECLARE @tablename VARCHAR(MAX)
    SET @tablename = 'SearchTMP' + @numbers

    UPDATE @SQLTbl 
    SET SQLStatement = N'SELECT * INTO ' + @tablename +' FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 


    DELETE FROM @SQLTbl 
    WHERE WHEREClause IS NULL 

    DECLARE @output TABLE (Id VARCHAR(50), Name VARCHAR(100)) 

    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 
        SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
        FROM @SQLTbl  
        WHERE ISNULL(Execstatus ,0) = 0 

        IF @GenerateSQLOnly = 0 
        BEGIN 
            IF OBJECT_ID('@tablename','U') IS NOT NULL 
                 DROP TABLE @tablename

            EXEC (@SQL) 

            IF EXISTS(SELECT 1 FROM @tablename) 
            BEGIN 
                --SELECT parsename(@tmpTblname,1) FROM SearchTMP 
                SELECT @MatchFound = 1 

                INSERT INTO @output (Id, Name)
                    SELECT * 
                    FROM [DynaForms].[dbo].[Enums_Tables] 
                    WHERE id IN (SELECT parsename(@tmpTblname, 1) FROM @tablename)
             END 
         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@SQL,'INTO @tablename','') 
         END 

         UPDATE @SQLTbl 
         SET Execstatus = 1 
         WHERE Tablename = @tmpTblname 
    END 

    SELECT * FROM @output
    --Select * from @SQLTbl

    IF @MatchFound = 0  
    BEGIN 
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 

    SET NOCOUNT OFF

Edit

I changed my stored procedure to have dynamic sql but it gives me this error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

I believe the problem is in my subquery, but I can't seen to make it work, could you please help me? The subquery is here:

DECLARE @SQLSUBQUERY VARCHAR(max);

SELECT @SQLSUBQUERY = 'SELECT * FROM [DynaForms].[dbo].[Enums_Tables] ';
SELECT @SQLSUBQUERY = @SQLSUBQUERY + 'WHERE id IN (SELECT  parsename(@tmpTblname,1) FROM @tablename)';

INSERT INTO @output (Id, Name)
    EXEC sp_executesql @SQLSUBQUERY

Full code here:

ALTER PROCEDURE [dbo].[SP_SearchTables] 
    @SearchStr NVARCHAR(60),
    @GenerateSQLOnly Bit = 0,
    @SchemaNames VARCHAR(500) ='%' 
AS 
    SET NOCOUNT ON 

    DECLARE @MatchFound BIT 
    SELECT @MatchFound = 0 

    DECLARE @CheckTableNames TABLE (Schemaname sysname, Tablename sysname) 

    DECLARE @SearchStringTbl TABLE (SearchString VARCHAR(500)) 

    DECLARE @SQLTbl TABLE (Tablename SYSNAME,
                           WHEREClause VARCHAR(MAX),
                           SQLStatement VARCHAR(MAX),
                           Execstatus BIT) 

    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableParamSQL VARCHAR(MAX) 
    DECLARE @SchemaParamSQL VARCHAR(MAX) 
    DECLARE @TblSQL VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
    DECLARE @ErrMsg VARCHAR(100) 

    IF LTRIM(RTRIM(@SchemaNames)) ='' 
    BEGIN 
        SELECT @SchemaNames = '%' 
    END 

    IF CHARINDEX(',',@SchemaNames) > 0  
        SELECT @SchemaParamSQL = 'SELECT ''' + REPLACE(@SchemaNames,',','''as SchemaName UNION SELECT ''') + '''' 
    ELSE 
        SELECT @SchemaParamSQL = 'SELECT ''' + @SchemaNames + ''' as SchemaName ' 

    SELECT @TblSQL = 'SELECT SCh.NAME,T.NAME 
              FROM SYS.TABLES T 
              JOIN SYS.SCHEMAS SCh 
              ON SCh.SCHEMA_ID = T.SCHEMA_ID 
              INNER JOIN [DynaForms].[dbo].[Enums_Tables] et on 
                 (et.Id = T.NAME COLLATE Latin1_General_CI_AS)  '

    INSERT INTO @CheckTableNames (Schemaname, Tablename) 
        EXEC(@TblSQL) 

    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 

IF LTRIM(RTRIM(@SearchStr)) ='' 
BEGIN 

    SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter' 
    PRINT @ErrMsg 
    RETURN 
END 
ELSE 
BEGIN  
    SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#') 
    SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#') 

    SELECT @SearchStr = REPLACE(@SearchStr,'''','''''') 

    SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + '''' 



    INSERT INTO @SearchStringTbl 
    (SearchString) 
    EXEC(@SQL) 

    UPDATE @SearchStringTbl 
       SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',') 
END 

INSERT INTO @SQLTbl (Tablename, WHEREClause) 
    SELECT 
        QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
        (SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10) 
         FROM SYS.columns SC 
         JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id 
                            AND STy.user_type_id =SC.user_type_id 
         CROSS JOIN @SearchStringTbl SearchSTR 
         WHERE 
             STY.name in ('varchar','char','nvarchar','nchar','text') 
             AND SC.object_id = ST.object_id 
         ORDER BY SC.name 
         FOR XML PATH('') 
        ) 
  FROM  SYS.tables ST 
  JOIN @CheckTableNames chktbls 
    ON chktbls.Tablename = ST.name  
  JOIN SYS.schemas SCh 
    ON ST.schema_id = SCh.schema_id 
   AND Sch.name        = chktbls.Schemaname 
 WHERE ST.name <> 'SearchTMP' 
  GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ; 

declare @numbers varchar(MAx)
set @numbers = convert(numeric(12,0),rand() * 899999999999) + 100000000000
declare @tablename varchar(MAX) 
set @tablename = 'SearchTMP' + @numbers

  UPDATE @SQLTbl 
     SET SQLStatement = N'SELECT * INTO ' + @tablename +' FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 


  DELETE FROM @SQLTbl 
   WHERE WHEREClause IS NULL 

 DECLARE @output TABLE (Id VARCHAR(50), Name VARCHAR(100)) 

WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
BEGIN 

    SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
      FROM @SQLTbl  
     WHERE ISNULL(Execstatus ,0) = 0 

     IF @GenerateSQLOnly = 0 
     BEGIN 

            DECLARE @SQLs NVARCHAR(MAX)
            SELECT @SQLs = 'DROP TABLE dbo.' + QUOTENAME(@tablename) + '';

        IF OBJECT_ID(''+@tablename+'','U') IS NOT NULL 
          EXEC sp_executesql @SQLs;


         EXEC (@SQL) 


        --IF EXISTS(SELECT 1 FROM @tablename) 
        BEGIN 
            --SELECT parsename(@tmpTblname,1) FROM SearchTMP 
            SELECT @MatchFound = 1 

            DECLARE @SQLSUBQUERY VARCHAR(max);
            SELECT @SQLSUBQUERY = 'SELECT * FROM [DynaForms].[dbo].[Enums_Tables] ';
            SELECT @SQLSUBQUERY = @SQLSUBQUERY + 'WHERE id IN (SELECT  parsename(@tmpTblname,1) FROM @tablename)';

            INSERT INTO @output (Id, Name)
            EXEC sp_executesql @SQLSUBQUERY
            --Select * from [DynaForms].[dbo].[Enums_Tables] where id in (SELECT parsename(@tmpTblname,1) FROM @tablename)
        END 

     END 
     ELSE 
     BEGIN 
         PRINT REPLICATE('-',100) 
         PRINT @tmpTblname 
         PRINT REPLICATE('-',100) 
         PRINT replace(@SQL,'INTO @tablename','') 
     END 

     UPDATE @SQLTbl 
        SET Execstatus = 1 
      WHERE Tablename = @tmpTblname 

END 

SELECT * FROM @output
--Select * from @SQLTbl


IF @MatchFound = 0  
BEGIN 
    SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
    PRINT @ErrMsg 
    RETURN 
END 

SET NOCOUNT OFF

You are trying to use @tablename as a table, but it is just a varchar(MAX) variable. — Denis Rubashkin
– Denis Rubashkin, Commented Jan 16, 2018 at 15:56
DROP TABLE @tablename What is this supposed to do? You can't drop a table variable (and it isn't even a table variable, as Denis points out). Even if you could, you are using it just two lines later. — HoneyBadger
– HoneyBadger, Commented Jan 16, 2018 at 15:56
@DenisRubashkin For what I have searched my making a select * into it will create a table, and since I want a dynamic name, I will give a name to that table. That I'm certain it will work. As a matter of fact, I check my database, and the table is there. The problem is in getting that table with tablename . — Aires Menezes
– Aires Menezes, Commented Jan 16, 2018 at 16:07
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Feb 5, 2018 at 20:39

sepupic · Accepted Answer · 2018-01-16 15:57:57Z

1

There are at least these errors:

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

This is wrong because check for a table with the name @tablename

IF EXISTS(SELECT 1 FROM @tablename)

This produce the error you've got.

DROP TABLE does not accept variable, use dynamic sql to to your drop

answered Jan 16, 2018 at 15:57

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aires Menezes Over a year ago

Thank you for your answer, I actually came up with that conclusion too, I will post again if I find some trouble.

Collectives™ on Stack Overflow

SQL Server - Dynamic Table Name as Variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related