Post reply

Where am i going wrong with this IF Exist Else

  • March 23, 2018 at 6:22 am

    #410357

    Can someone please let me know where i am going wrong in this code snippet...its when i try to place the set @Loop = @Loop + 1.

    Basically trying to go thru a list and if the table already exists then to skip it and move to the next.

    All  comments and help super appreciated.  Thanks

    DECLARE @Loop INT,
       @cmd VARCHAR(MAX),
       @rc INT,
            @TB VARCHAR(130) 

    -- I am creates a new temporary table with a sequential ID (and no gaps), 
    -- so I can loop by it:
    use AS400Schema
    SELECT ROWID = IDENTITY(INT,1,1),TABLENAME
    into #Tables
    FROM [AS400Schema].[dbo].[schemanames] where TableSchema = 'AECF' and TableType = 'Base Table'
    ORDER BY TABLENAME 

    SET @rc = @@ROWCOUNT 

    SET @Loop = 1 

    -- Looping on table names, dropping and recreating each: 
    WHILE @Loop <= @rc 
    BEGIN
     SELECT @TB = TABLENAME FROM #Tables WHERE ROWID = @Loop 

     SET @cmd = ''  /*Set @cmd = 'use test3 IF EXISTS (SELECT 1 FROM sys.objects WHERE Type = ''U''' +
          'AND name = ''' + @TB + ''') DROP TABLE [' + @TB + '];' +
          'SELECT * INTO dbo.[' + @TB + '] ' +
          'FROM [AS400Schema].[dbo].[schemanames]' */
     --Print @cmd         
          
    /******  My issue is in here someplace   ****/
     SELECT @cmd = 'use AEC_BPCS IF EXISTS (SELECT 1 FROM sys.objects WHERE Type = ''U''' +
          'AND name = ''' + @TB + ''') BEGIN SET '@Loop = @Loop + 1' END' + ' ELSE '+
          'SELECT * INTO dbo.[' + @TB + '] ' +
          'FROM [AS400].[RCHASE5C].[AECF].[' + @TB + ']'
                    
        Print @cmd 

     EXEC (@cmd)
     SET @Loop = @Loop + 1 
    END

    DROP TABLE #Tables

    DHeath

  • March 23, 2018 at 6:54 am

    #1984398

    What error is being thrown, or what is it doing that it's not supposed to?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 23, 2018 at 7:02 am

    #1984399

    Thanks for the reply... The error is
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '@Loop = @Loop + 1'.

    If there is another way to loop thru this i am okay with it but i know its VERY close to being there..

    DHeath

  • March 23, 2018 at 7:29 am

    #1984405

    Remove the single quote around @Loop = @Loop + 1

    AND name = ''' + @TB + ''') BEGIN SET '@Loop = @Loop + 1' END' + ' ELSE '+

  • March 23, 2018 at 7:48 am

    #1984407

    Thank you for your reply

    When i remove the single quote that is around the @Loop = @Loop + 1  i still get the same error. which is
    Msg 102, Level 15, State 1, Line 39
    Incorrect syntax near '@Loop'.

    I can make the code run PERFECTLY  as long as i am  NOT using the ELSE  but i need to use that so that i can pick up where it left off (table constraint error) and NOT re-import a few hundred thousand entries and 2+ hours time.  hahaha

    DHeath

  • March 23, 2018 at 8:04 am

    #1984410

    So your code now looks like this?:
    ' +
        'FROM [AS400].[RCHASE5C].[AECF].[' + @TB + ']'"]

    I'm not getting a syntax error with it.

  • March 23, 2018 at 8:10 am

    #1984412

    Actually, I think the problem is your dynamic SQL doesn't recognize the variable (@Loop)
    ' +
        'FROM [AS400].[RCHASE5C].[AECF].[' + @TB + '] END' "]

  • March 23, 2018 at 8:15 am

    #1984413

    Thanks so much for the help... it compiles and runs but returns this error
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@Loop".

    Which i dont understand because its declared at the very top as my very first declaration

    Thanks for helping.

    DHeath

  • March 23, 2018 at 8:29 am

    #1984416

    You also need to declare the @Loop variable in your dynamic SQL because it executes a s separate batch* and so any previously declared variables will not be available.  Try changing the affected part to this:

    SELECT @cmd = 'use AEC_BPCS
    DECLARE @loop INT
    IF EXISTS (SELECT 1 FROM sys.objects WHERE Type = ''U''' +
    'AND name = ''' + @TB + ''') BEGIN SET @Loop = @Loop + 1 END' + ' ELSE '+
    'SELECT * INTO dbo.[' + @TB + '] ' +
    'FROM [AS400].[RCHASE5C].[AECF].[' + @TB + ']'
    Print @cmd

    * I'll stand to be corrected on the details here but the point is the same.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • March 23, 2018 at 8:45 am

    #1984418

    SUCCESS!!!!!!!!

    Thank to all... Greatly appreciated on all the assistance from anyone that replied or read the post

    Currently  the code is running and like i  said  hopefully i have my logic correct as to where it will only populate new tables and not ones that are already in existence.

    Thanks again

    DHeath

  • March 23, 2018 at 9:39 am

    #1984427

    Just wanted to point out that the @loop increment inside the dynamic SQL will not carry through to @loop increment outside the dynamic SQL
    Again it depends how you are actually using this variable . Pointing it out as I see you are incrementing @loop twice

    Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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