Raiserror ( Experts plz comment )

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Raiserror ( Experts plz comment )

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-19 : 16:24:30

I was using raiserror to transfer the control tor catch block, however
in the below case it is also executing the stm next to raise err, which I do not want it to.

one way is to use goto, which I want to avoid.. until no other option.
[added later]: now when I tried with goto it is still executing the stm SELECT A into a ( I want to skip this stm on the below if condition if passess)


begin try

DECLARE @column_name VARCHAR(100)
DECLARE @table_Name VARCHAR(100)
DECLARE @msg VARCHAR(500)
 
 SELECT @column_name= column_name
    ,@table_name=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'etc_pd' and column_name ='raw'
 IF @column_name = 'raw'
 BEGIN
  SET @msg= 'etc_pd has problem exit at this point'
  RAISERROR( @msg,11,1)
 END


 SELECT A into a 
END TRY

BEGIN CATCH
 SELECT  ERROR_MESSAGE() ,ERROR_LINE()  AS [Error Line]
END CATCH

Msg 207, Level 16, State 1, Line 17
Invalid column name 'A'.

It should not execute the select stm, the control should exit from the raiserror and execute the stm in catch & exit. if the if stm passes.

-Neil

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-19 : 16:53:15

Make couple of changes to your code as shown below in red:

begin try

DECLARE @column_name VARCHAR(100)
DECLARE @table_Name VARCHAR(100)
DECLARE @msg VARCHAR(500)
 
 SELECT @column_name= column_name
    ,@table_name=table_name 
 FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'etc_pd' and column_name ='raw'
 IF @column_name IS NULL
 BEGIN
  SET @msg= 'etc_pd has problem exit at this point'
  RAISERROR( @msg,11,1)
 END


 SELECT A into a FROM etc_pd
END TRY

BEGIN CATCH
 SELECT  ERROR_MESSAGE() ,ERROR_LINE()  AS [Error Line]
END CATCH

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-19 : 17:08:00

Hi Sunita,

my requirement here is,if the condition (IF @column_name = 'raw') is true then etc_pd table does not exists hence throw the error msg as in the eg. and exit the code.

if the condition fails then the table exists and perform business logic and then exit.

I hope I am not confusing :)

-Neil

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-19 : 17:21:02

I thought it was the other way around. if @column_name came back null then the table named table_name does not exist (or there is no column named raw in that table).

But even so, i.e., even if you changed the if condition to @column_name IS NULL as I had indicated, you would still get an error if you don't make the second change that I had indicated. I don't completely understand the phases that query execution goes through - but my conjecture is that when SQL server tries to bind the statement "Select A into A", it finds that it cannot bind the column A to anything and hence the error. Even with late-binding and all that sophisticated stuff, seems like it does something with the else clause even if it was not going to get executed.

Would be nice to see the interpretation from some of the experts on this forum.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-19 : 17:38:10

Yes you are right, I tried to move the control stm away from select by using if( which has error) still it errors, may be because before it executes it try's to compile and fails there.

But what if I want it in my way as explained earlier do i need to use dynamic sql? I am sure this should be possible in SQL Server, it works in Oracle with WHEN OTHER exception.

any experts plz comment...

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-19 : 17:57:05

Visakh sir you there???

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-19 : 21:28:41

didnt we discuss and resolved it here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177993

In any case the error will be thrown as its a compile time error. Can you explain why you're explicitly trying to pass a syntactically wrong statement? I didnt understand the purpose of this code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-20 : 03:12:15

oh yes we discussed this, I was still looking for some solution:)

Now why I am trying to pass wrong stm: Because this stm uses the table which will be created prior if the data file exists hence it will have all valid columns in this select stm. if the data file do not exists then the system will create a dummy table with only one column 'Raw' hence I was trying to handle this situation, by the if condition.

Now I have put the select with err stm in the Dynamic statement and using EXEC, then it is working fine.

Thanks

-Neil

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 06:57:01

The statement "SELECT A into A" generates an error under any circumstances. I think of it as a syntax error. Yet, the parser lets it through without complaining. What I was trying to understand is, if the parser lets it through (i.e., if it is not a syntax error) then what kind of error is it?

If your query has no syntax errors (including the "select a int a" type of errors) the strategy you are trying to implement would work (subject to the list that Visakh quoted in the other thread).

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-20 : 18:23:19

Thanks Sunita,

I have given Select a into a, intentional err stm; as I said that ,this will have list of column with a valid table only if the data file exists. if the file do not exists then also this stm will be there in the code, which can be allowed to fail and throw the user msg to front end.

But this was failing as, it was trying to compile first and throwing error. hence I made it dynamic to get it compiled at run time. and the control will go this stm by passing through the IF which allows the ctrl to pass only if it contains valid table name and columns names.

Regards,

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 18:46:09

quote:
Originally posted by aakcse

Thanks Sunita,

I have given Select a into a, intentional err stm; as I said that ,this will have list of column with a valid table only if the data file exists. if the file do not exists then also this stm will be there in the code, which can be allowed to fail and throw the user msg to front end.

But this was failing as, it was trying to compile first and throwing error. hence I made it dynamic to get it compiled at run time. and the control will go this stm by passing through the IF which allows the ctrl to pass only if it contains valid table name and columns names.

Regards,

-Neil

whatever you're trying is not possible

so if you're sure that table wont exists if no data in data file you should use a IF condition check based OBJECT_ID() function to see if table exists and if yes do the select. This was what Rob suggested in other thread as well I believe

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources