Query help - SQL Server Forums

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

Query help

Author

Topic

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2007-06-01 : 13:32:23

Create procedure dbo.dup_test_sp
AS
declare @i int
set @i = 5
START:
--PRINT '1'

insert into dup_test(id)
select @i

if @@error <> 0
BEGIN
set @i=@i+1
GOTO START
END
else
begin
goto finish
end

finish:

SELECT @i

Table has primary key on id feild
If i run first time it will add 5 in table. It will break next time because duplicate but i have go to statment so it will go to top and run again and get next no 6 and add into table. It return 6 with error message and due to that error message my page is breaking.
Is there any way that i can hide the error message when it return value from sql.

Here is error message i am getting
Server: Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK_dup_test'. Cannot insert duplicate key in object 'dup_test'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)

But it return 6 too. Can i hide the errormessage so my page wont break

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-01 : 13:42:45

You need to check before you do the INSERT.


IF NOT EXISTS( SELECT * FROM Dup_Test WHERE Id = @i)
BEGIN
   INSERT INTO...


   IF @@ERROR <> 0...

END

Not sure what you are trying to achieve with the GOTO/START stuff.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources