Post reply

Raise error

  • March 30, 2007 at 1:20 pm

    #119269

    Hi,

     i need help on this

     

    i have a table A which has the following two column

    ID,value

    ID is my primary key

     

    i also have a table B which has the following two column

    ID,value

    ID is my primary key

    Note: i want to do insert/update from table A TO table B

    i want to RAISE ERROR in the following situation:

     

    if table A has null value raise error 'please enter your ID'

    if ID does not exists and is not null either insert into table B

    if  ID exists alreay update it

     

     

     

     

  • March 30, 2007 at 3:37 pm

    #697950

    "if table A has null value raise error 'please enter your ID'"

    Wouldn't this be the same as your post from here:

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=354363

    except that you would want to stop the UPSERT if there is a NULL value for TableA.Value?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • March 30, 2007 at 6:51 pm

    #697957

    it is similar to that but i want to know how to raise error in this situation

  • March 30, 2007 at 7:13 pm

    #697958

    SQL is data access language.

    It does not have user dialogue options.

    You may query for lines with ID IS NULL, but interaction with user must be done from front-end application written on proper programming language.

    _____________
    Code for TallyGenerator

  • April 2, 2007 at 7:29 am

    #698076

    If [id] is the primary key for table B, then it is not possible to have a NULL [id]. Please post actual CREATE TABLE statements.

  • April 2, 2007 at 8:29 am

    #698099 

    You can create your own error message with RAISEERROR provided the message number is greater than 50,000 and severity levels from 0 through 18 as in: 
    USE master
    EXEC sp_addmessage 50001, 16,  'Please enter your ID.'

    In your trigger or stored procedure you can RAISERROR (50001, 16, 1)

     

    or you can do:

     

    IF @id=’’
    BEGIN
       RAISERROR ('Please enter your ID.',16, 1)
       ROLLBACK TRANSACTION
    END

     

    Then, you can catch that error in your front-end app. For example, you can use Try...Catch statements in VB.Net to catch the exception that is thrown when SQL Server returns a warning or error as in:

     

    Try

    …

     

    Catch exp As SqlException

     

                Select Case exp.Number

                    Case 50001

                        MsgBox("Your ID number must be entered.", MsgBoxStyle.Critical, "SQL Error " + exp.Number.ToString)

                    Case Else …

    End Select

     

    End Try

     

Viewing 6 posts - 1 through 5 (of 5 total)

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