Where am i going wrong with this IF Exist Else

Question

Where am i going wrong with this IF Exist Else

DHeath

SSCertifiable

Points: 5200
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

DHeath SSCertifiable Points: 5200 More actions · Answer 2

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

Bert-701015 SSCommitted Points: 1615 More actions · Answer 3

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

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

DHeath SSCertifiable Points: 5200 More actions · Answer 4

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

Bert-701015 SSCommitted Points: 1615 More actions · Answer 5

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

I'm not getting a syntax error with it.

Bert-701015 SSCommitted Points: 1615 More actions · Answer 6

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

DHeath SSCertifiable Points: 5200 More actions · Answer 7

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

Neil Burton SSC-Insane Points: 22999 More actions · Answer 8

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

DHeath SSCertifiable Points: 5200 More actions · Answer 9

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

@Taps SSC Eights! Points: 879 More actions · Answer 10

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