RE: [sqr-users] if not exists (select * from sysobjectswherename='<>') = processing should stop

["Steve Cavill" <steve.cavill@infoclarity.com.au>]

One advantage of putting it in the setup section is you can trap non-existent 
table errors that would otherwise cause the program to
not compile.

The order of processing is -
1. compile the setup section
2. execute the setup section
3. compile the program section (and the rest of the program), including all the 
sql.
4. execute the program section.

So for example you can create tables in the setup section that are used later 
in the program.  If you try to create the tables in
the program section and then select from the same tables, your program won't 
compile because the tables don't exist at compile time.
So as a general rule, I would recommend putting pre-processing sql in the setup 
section.

The problem you are facing is that the sql sections are all compiled before 
they are executed, so it will compile the second sql
even if the first sql produces your stop condition.  But I'm surprised your 
second sql actually executes.
That would suggest to me that sqr does not recognise
raiserror 20004
As an error condition.

Steve.

-----Original Message-----
From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org
[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf 
Of Bob Stone
Sent: Friday, 28 April 2006 7:23 AM
To: This list is for discussion about the SQR database reporting languagefrom 
Hyperion Solutions.
Subject: RE: [sqr-users] if not exists (select * from sysobjects 
wherename='<>') = processing should stop

I'm not sure it matters if you do the setup or not.  If it's in your first 
procedure of the program/report section of your program,
it should stop early enough for you.  I don't think you gain anything by having 
it in the setup instead of a bit further in the
program.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of Jay 
Sagadraca
Sent: Thursday, April 27, 2006 4:17 PM
To: sqr users
Subject: [sqr-users] if not exists (select * from sysobjects where
name='<>') = processing should stop

Hello Gurus,
   
      Thank you for your help.  But 1 more question please.  How about in the 
begin-setup area?  Can we stop the whole process if
the table is not created? 
   
    begin-setup
begin-sql on-error=stop
if not exists (select 1 from sysobjects where name =
'Transaction_Table')
begin
  raiserror 20004 "Table: Transaction_Table do not exist " 
end
end-sql
begin-sql
if exists ( select 1 from sysobjects where name = 'Clients') 
   begin
       print "Found the table ..."
      end
  end-sql
end-setup 
   
  Thanks,
   




Jay V. Sagadraca

Markham, Ontario, Canada

email : knight7883@yahoo.ca

                
---------------------------------
Make Yahoo! Canada your Homepage Yahoo! Canada Homepage  

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users