Raise error

Question

Post reply

Raise error

Best-330874

SSCrazy

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

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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

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

http://www.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

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

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

Sergiy SSC Guru Points: 110208 More actions · Answer 3

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

vadba SSChampion Points: 11133 More actions · Answer 4

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.

alonzo SSC-Addicted Points: 443 More actions · Answer 5

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