locked
Trying to use Dynamic SQL to drop columns with date like last month RRS feed

  • Question

  • User-718146471 posted

    Ok, I'm stuck again. I thought this was going to be no big deal however, I am getting an error that says "Must declare the scalar variable." This would be totally fine except I did declare it. Here is my proc now with my edits. Everything up to the point of dropping last months columns works like a charm.

    USE CVEMIDB
    GO
    declare @tablename varchar(500)
    declare @sql varchar(5000)
    declare @idname varchar(50)
    declare @tablearchive varchar(500)
    declare @lastmonth nvarchar(MAX)
    SET @lastmonth = 'D' + cast(year(getdate()) as char(4)) + right('0' + cast(month(getdate())-1 as varchar), 2) + '__'
    --Select all the tables which you want to make in archive
    declare tableCursor cursor FAST_FORWARD FOR 
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES
    where table_name = 'CVECountsByDate'
    --Put your condition, if you want to filter the tables
    --like '%TRN_%' and charindex('Archive',table_name) = 0 and charindex('ErrorLog',table_name) = 0
    --Open the cursor and iterate till end
    OPEN tableCursor 
    FETCH NEXT FROM tableCursor INTO @tablename     WHILE @@FETCH_STATUS = 0
          BEGIN
                 set @tablearchive =  @tablename+'_Archive'
                 --check for the table exists, not, create it
                IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME= @tablearchive) 
                      begin 
                            SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
                            EXEC(@sql)
                      END
                 --check the structure is same, if not, create it
                IF exists (select column_name from 
                INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME=@tablearchive)) begin SET @sql = 'drop table ' + @tablearchive EXEC(@sql) SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2' EXEC(@sql) end --Check if the table contains, identify column,if yes, then it should be handled in different way --You cannot remove the identity column property through T-SQL --Since the structure of both tables are same, the insert fails, as it cannot insert the identity column --value in the archive table IF EXISTS(SELECT * FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),
    'TableHasIdentity') != 0) BEGIN --Select the identity column name automatically select @idname = column_name from information_schema.columns where columnproperty(object_id(table_name),column_name,'isidentity')=1 AND table_name = @tablearchive --Remove the column SET @sql = 'ALTER TABLE ' + @tablearchive + ' DROP COLUMN ' + @idname EXEC(@sql) --Create the column name again (not as identity) --archive table does require identity column SET @sql = 'ALTER TABLE ' + @tablearchive + ' ADD ' + @idname+ ' INT' EXEC(@sql) END SET @sql = 'insert into ' + @tablearchive +' select * from '+ @tablename EXEC(@sql) FETCH NEXT FROM tableCursor INTO @tablename SET @sql = 'ALTER TABLE ' + @tablename + ' DROP COLUMNS IF EXISTS (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE
    INFORMATION_SCHEMA.TABLE_NAME = ' + @tablename + ' AND COLUMN_NAME LIKE @lastmonth)' EXEC(@sql) END CLOSE tableCursor DEALLOCATE tableCursor

    So exactly what the heck is wrong? Refer to previous post: https://forums.asp.net/t/2106031.aspx?Archival+Stored+Procedure

    Monday, October 31, 2016 12:47 PM

Answers

  • User-718146471 posted

    Duh, I could smack myself in the forehead. Table_name has to be surrounded by quotation marks. That is why it isn't working. Thanks folks!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 8:45 PM

All replies

  • User2117486576 posted

    SET @sql = 'ALTER TABLE ' + @tablename + ' DROP COLUMNS IF EXISTS (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE
    INFORMATION_SCHEMA.TABLE_NAME = ' + @tablename + ' AND COLUMN_NAME LIKE @lastmonth)'

    @lastmonth is included in the query text.  I think you want to concatenate the the value of the parameter @lastmonth to the query text.

    Monday, October 31, 2016 3:25 PM
  • User-718146471 posted

    I've been doing some experimenting and I think I am really close to nipping this one in the bud. When I do a select query to output to the console, it shows the query as correct:

    ALTER TABLE CVECountsByDate DROP COLUMNS IF EXISTS (SELECT COLUMN_NAME Columns 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = CVECountsByDate AND COLUMN_NAME LIKE D201609__

    However, when I try to execute the same declared clause, it gives me this:

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near 'D201609__'.

    Now, this is how my dynamic SQL reads for this section:

    declare @PrevMonth nvarchar(MAX) = 'D' + cast(year(getdate()) as char(4)) + right('0' + cast(month(getdate())-1 as varchar), 2) + '__'
    DECLARE @DynSql nvarchar(MAX) = 'ALTER TABLE ' + @tablename + ' DROP COLUMNS IF EXISTS 
    (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' + @tablename + '
    AND COLUMN_NAME LIKE ' + @PrevMonth + '' SELECT (@DynSql) EXEC(@DynSql)

    It seems I need to do something with the @PrevMonth declared variable but I'm kind of stumped. When I execute the alter SP, it takes it just fine, it is only when I execute it, it goes sideways. I've even tried the query this way:

    DECLARE @DynSql nvarchar(MAX) = 'ALTER TABLE ' + @tablename + ' DROP COLUMNS IF EXISTS 
    (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' + @tablename + '
    AND COLUMN_NAME LIKE ''' + @PrevMonth + ''''

    Which outputs like this:

    ALTER TABLE CVECountsByDate DROP COLUMNS IF EXISTS (SELECT COLUMN_NAME Columns FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = CVECountsByDate AND COLUMN_NAME LIKE 'D201609__'

    I know it has to be something simple but I think I've looked at the query code for too long. Thanks to anyone who can help me solve this riddle.

    Monday, October 31, 2016 7:57 PM
  • User-718146471 posted

    Duh, I could smack myself in the forehead. Table_name has to be surrounded by quotation marks. That is why it isn't working. Thanks folks!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 8:45 PM